About the SQL Server MERGE Statement

The  SQL Server MERGE statement combines the sequence of conditional INSERT, UPDATE, and DELETE statements in a single atomic statement, depending on the existence of a record. In other words, you can sync two different tables so that the content of the target table is modified based on differences found in the source table.

The main application area for MERGE is a data warehouse environment, where tables need to be refreshed periodically with new data arriving from online transaction processing (OLTP) systems. This new data may contain changes to existing rows in tables and/or new rows that need to be inserted. If a row in the new data corresponds to an item that already exists in the table, an UPDATE or a DELETE statement is performed. Otherwise, an INSERT statement is performed.

The alternative way, which you can use instead of applying the MERGE statement, is to write a sequence of INSERT, UPDATE, and DELETE statements, where, for each row, the decision is made whether to insert, delete, or update the data. This old approach has significant performance disadvantages: it requires multiple data scans and operates on a record-by-record basis.

Examples 1 and 2 show the use of the MERGE statement.

EXAMPLE 1

 

Example 1 creates the bonus table, which contains one row, (p1, 100). This table will be used for merging.

EXAMPLE 2

 

The MERGE statement in Example 2 modifies the data in the bonus table depending on the existing values in the pr_no column. If a value from the project_no column of the project table appears in the pr_no column of the bonus table, the MATCHED branch will be executed and the existing value will be updated. Otherwise, the NON MATCHED branch will be executed and the corresponding INSERT statement will insert new rows in the bonus table.

The content of the bonus table after the execution of the MERGE statement is as follows:

 

 

From the result set, you can see that a value of the bonuscolumn represents 10 percent of the original value in the case of the UPDATE statement, and 5 percent in the case of the UPDATE statement, and 5 percent in the case of the INSERT statement.