Microsoft SQL Server MERGE MERGE to Insert / Update / Delete
Example
MERGE INTO targetTable
USING sourceTable
ON (targetTable.PKID = sourceTable.PKID)
WHEN MATCHED AND (targetTable.PKID > 100) THEN
DELETE
WHEN MATCHED AND (targetTable.PKID <= 100) THEN
UPDATE SET
targetTable.ColumnA = sourceTable.ColumnA,
targetTable.ColumnB = sourceTable.ColumnB
WHEN NOT MATCHED THEN
INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);
WHEN NOT MATCHED BY SOURCE THEN
DELETE
; --< Required
Description:
MERGE INTO targetTable- table to be modifiedUSING sourceTable- source of data (can be table or view or table valued function)ON ...- join condition betweentargetTableandsourceTable.WHEN MATCHED- actions to take when a match is found-
AND (targetTable.PKID > 100)- additional condition(s) that must be satisfied in order for the action to be taken
THEN DELETE- delete matched record from thetargetTableTHEN UPDATE- update columns of matched record specified bySET ....WHEN NOT MATCHED- actions to take when match is not found intargetTableWHEN NOT MATCHED BY SOURCE- actions to take when match is not found insourceTable
Comments:
If a specific action is not needed then omit the condition e.g. removing WHEN NOT MATCHED THEN INSERT will prevent records from being inserted
Merge statement requires a terminating semicolon.
Restrictions:
WHEN MATCHEDdoes not allowINSERTactionUPDATEaction can update a row only once. This implies that the join condition must produce unique matches.
