Update, Insert, & Delete in One Shot
Wade Cantley
CRUD Throwback
- Mix the Sprite and vodka in a shaker with some ice to chill it.
- Strain into glass.
- 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.
- 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;