Using the MERGE statement

In SQL Server, you can perform multiple DML operations in a single code block using the MERGE statement. The MERGE statement is a powerful Transact-SQL language feature that allows you to join a source table with a target table, and then perform multiple DML operations against the specified target table, based on the results of the MERGE statement join conditions. By using a MERGE statement, you can improve the performance of OLTP applications, since the data is processed only once.

To execute a MERGE statement, a user must at least have a SELECT permission assigned on the source table and INSERT, UPDATE, and DELETE permissions assigned on the target table.

The basic syntax for the MERGE statement is as follows:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ]
    USING <source_table>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND &lt...