merge in sql server code example

Example 1: tsql merge example

MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED 
     THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Example 2: tsql goup by clause in merge statement

MERGE dbo.MyTarget targ
USING (SELECT ... FROM dbo.MySource GROUP BY .....) src
ON (targ.Identifier = src.Identifier
    AND targ.Name = src.ConstituentName
    AND targ.Ticker = src.ConstituentTicker
    AND (targ.CUSIP = src.CUSIP OR targ.ISIN = src.ISIN OR targ.SEDOL = src.SEDOL))
WHEN MATCHED THEN
-- update values
;

Example 3: t-sql merge example

MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

Example 4: sql merge statement

MERGE LoginTypes T
        USING (SELECT 'System' as Description) S
        ON(S.Description = T.Description)
WHEN NOT MATCHED BY TARGET
    THEN INSERT(Description, CreatedTimestamp, LastUpdatedTimestamp)
VALUES('System', getdate(), getdate());