merge 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
;
Example 3: merge clause with inner join
MERGE table1
USING (SELECT table3.keycolumn,
table2.DataColumn1,
table2.DataColumn2
FROM table2
INNER JOIN table3
ON table2.anotherKey = table3.anotherKey
WHERE table2.anotherKey = 'A1') tmpTable
ON
table1.keyColumn = tmpTable.keyColumn
WHEN MATCHED THEN
UPDATE
SET table1.DataColumn1 = tmpTable.DataColumn1
,table1.DataColumn2 = tmpTable.DataColumn2;
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());