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-- Oracle: Example for Insert or update in t1 from t2 values
2MERGE INTO table1 t1
3USING table2 t2
4ON (t1.CODE = t2.ID)
5WHEN MATCHED THEN
6 UPDATE SET t1.COL1 = t2.VALUE1
7WHEN NOT MATCHED THEN
8 INSERT (CODE, COL1) VALUES (t2.ID, t2.VALUE1);
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;
1MERGE LoginTypes T
2 USING (SELECT 'System' as Description) S
3 ON(S.Description = T.Description)
4WHEN NOT MATCHED BY TARGET
5 THEN INSERT(Description, CreatedTimestamp, LastUpdatedTimestamp)
6VALUES('System', getdate(), getdate());