tsql merge example

1MERGE esqlProductTarget T
2USING esqlProductSource S
3ON (S.ProductID = T.ProductID)
4WHEN MATCHED 
5     THEN UPDATE
6     SET    T.Name = S.Name,
7            T.ProductNumber = S.ProductNumber,
8            T.Color = S.Color
9WHEN NOT MATCHED BY TARGET
10THEN INSERT (ProductID, Name, ProductNumber, Color)
11     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
12WHEN NOT MATCHED BY SOURCE
13THEN DELETE;

1MERGE dbo.MyTarget targ
2USING (SELECT ... FROM dbo.MySource GROUP BY .....) src
3ON (targ.Identifier = src.Identifier
4    AND targ.Name = src.ConstituentName
5    AND targ.Ticker = src.ConstituentTicker
6    AND (targ.CUSIP = src.CUSIP OR targ.ISIN = src.ISIN OR targ.SEDOL = src.SEDOL))
7WHEN MATCHED THEN
8-- update values
9;

1
2        
3            
4        
5     MERGE sales.category t 
6    USING sales.category_staging s
7ON (s.category_id = t.category_id)
8WHEN MATCHED
9    THEN UPDATE SET 
10        t.category_name = s.category_name,
11        t.amount = s.amount
12WHEN NOT MATCHED BY TARGET 
13    THEN INSERT (category_id, category_name, amount)
14         VALUES (s.category_id, s.category_name, s.amount)
15WHEN NOT MATCHED BY SOURCE 
16    THEN DELETE;
17

1MERGE table1
2USING (SELECT table3.keycolumn,
3              table2.DataColumn1,
4              table2.DataColumn2
5       FROM table2
6       INNER JOIN table3
7           ON table2.anotherKey = table3.anotherKey
8       WHERE table2.anotherKey = 'A1') tmpTable
9ON 
10   table1.keyColumn = tmpTable.keyColumn
11WHEN MATCHED THEN
12       UPDATE
13       SET table1.DataColumn1 = tmpTable.DataColumn1
14            ,table1.DataColumn2 = tmpTable.DataColumn2;