MERGE statemen runs insert, update, or delete operations on a target table from the results of a join with a source table. At least one of the three MATCHED clauses must be specified, but they can be specified in any order.
The MERGE statement requires a semicolon (;) as a statement terminator. Otherwise, it will throw an exception "Error 10713 is raised when a MERGE statement is run without the terminator."
MERGE
Target_Table AS T
USING
Source_Table AS S
ON T.Column1 = S.Column1 AND T.Column2 = S.Column2
WHEN MATCHED
THEN UPDATE SET T.Column3 = S.Column3
WHEN NOT MATCHED BY TARGET
THEN INSERT (Column1, Column3, Column3)
VALUES (S.Column1, S.Column3, S.Column3)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
;
WHEN MATCHED THEN
Specifies that the records in Source table are macheted
with Target Table. Usually, we use this condition to UPDATE the records in Target table.
WHEN NOT MATCHED [ BY TARGET ] THEN
Specifies that the records in Source table are not matched with Target table, that means those records are not exists in Target,. Usually we use this condition to INSERT the new data into Target table.
WHEN NOT MATCHED BY SOURCE THEN
Specifies that the records in Target table are not matched with Source table, that means those records are not in Source,. Usually we use this condition to DELETE the data from Target table.
Since MERGE statement is combing all 3 DML commands into one command, will improve the query performance because the operations are performed within a single statement. This will reduce the number of times the data in the source and target tables are processed.
The MERGE statement can have, at most, two WHEN MATCHED clauses.
The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses.
The MERGE statement can have only one WHEN NOT MATCHED BY TARGET clause.
For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but doesn't guarantee on which action to fire triggers first or last.
Next