t sql merge example

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 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());