Contact Us

Use the form on the right to contact us.

You can edit the text in this area, and change where the contact form on the right submits to, by entering edit mode using the modes on the bottom right. 


Oak Ridge, TN, 37830
United States

Sql-Sangria

Update, Insert, & Delete in One Shot

Wade Cantley

CRUD Throwback

  1. Mix the Sprite and vodka in a shaker with some ice to chill it.
  2. Strain into glass.
  3. Using a spoon turned upside down angled into the vodka mix just to slightly break the surface, very slowly pour the blue curacao. It must be done with patience, or the two will mix and you won’t get the same effect. You’ll see the white layer begin to exist.
  4. Next add in the grenadine. The easiest way to do this is to pour close to the ednge of the glass to create an almost waterfall effect inside the glass. Since the grenadine is so thick, it’ll drop to the bottom of the glass.
  • 1/3 ounce Sprite *
  • 1/3 ounce lemon vodka *
  • 2/3 ounce blue curacao
  • 2/3 ounce grenadine
  • ice
  • *Note: Can use Mike's Hard Lemonade or Smirnoff Ice in place of these

So the idea here is to have MSSQL look at the data from the newFood table and if it is truly new, then insert the name, ID, and Type into the CurrentFood table.

If the IDs match but the values don't match update to change the values in the target to match the source.

If it isn't in the updated food table, then delete it.


;WITH cte_UpdatedFoods AS
(
     SELECT FoodName, FoodId
     FROM tblUpdatedFood NF
)
MERGE tblCurrentFood CF
USING cte_UpdatedFoods UF ON UF.foodId = CT.foodId
WHEN NOT MATCHED BY TARGET THEN
     INSERT (FoodName, FoodId, Type) VALUES (UF.FoodName, UF.FoodId, UF.Type)
WHEN MATCHED AND TARGET.foodName <> SOURCE.foodName OR TARGET.type <> SOURCE.type THEN
    UPDATE SET TARGET.foodName = SOURCE.foodName
      , TARGET.type = SOURCE.type
WHEN NOT MATCHED BY SOURCE THEN
     DELETE;