SQL Server 2014 Development Essentials

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> ...