In this article, we will review SQL Server MERGE statement, alternatives to MERGE statement, different clauses and examples of MERGE statement in SQL Server.

We will cover the following topics about SQL Server Merge Statement with some interesting examples in this post:

1. Introduction and Syntax of Merge Statement in SQL Server
2. Using Merge Statement to update, insert and delete rows in SQL Server tables
3. Working with TOP clause in Merge Statement
4. OUTPUT clause in Merge Statement
5. Merge with triggers in SQL Server

Merge Statement Introduction

MERGE statement is used to synchronize two tables by inserting, deleting, and updating the target table rows based on the join condition with the source table. Let us discuss a few examples on the MERGE statement using demo tables.

Syntax of MERGE

Below is the syntax of the MERGE statement in SQL Server.

Here I am creating sample tables to show different examples of the MERGE statement.

Use below T-SQL script to create tables and insert sample data into tables.

Using MERGE to update matched rows

WHEN MATCHED clause in SQL Server MERGE statement is used to update, delete the rows in the target table when the rows are matched with the source table based on the join condition. In this case, Locations is the target table, Locations_stage is the source table and the column LocationID is used in the join condition. Please refer to the below T-SQL script for updating matched rows using WHEN MATCHED clause.

Rows with LocationID 1 and 3 are matched in the target and source table as per the join condition and the value of LocationName in the target was updated with the value of LocationName in the source table for both rows.

We can also use additional search condition along with “WHEN MATCHED” clause in SQL Server MERGE statement to update only rows that match the additional search condition.

We can see that the merge statement did not update the row with LocationID 1 as it did not satisfy the additional search condition specified along with the WHEN MATCHED clause.

At most, we can specify only two WHEN MATCHED clauses in the MERGE statement. If two WHEN MATCHED clauses are specified, one clause must have an update operation and the other one must use delete operation. Please refer to below T-SQL script for the example for the MERGE statement with two WHEN MATCHED clauses.

• Note: We cannot use the same DML operation in both WHEN MATCHED clauses.

When there is more than one row in the source table that matches the join condition, the update in SQL Server MERGE statement fails and returns error “The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

Using MERGE to insert rows in Target table

WHEN NOT MATCHED BY TARGET clause is used to insert rows into target table that does not match join condition with a source table. WHEN NOT MATCHED BY TARGET clause can be specified only once in the SQL Server MERGE statement.

For example, the row with LocationID = 4 in the table Locations_stage does not match join condition and is present in the source table only. Now when we use WHEN NOT MATCHED BY TARGET clause in the merge statement to insert the additional row from Locations_stage into Locations.

We can use additional search condition to filter the rows inserted into the target table. In this case, the rows which do not match with join condition but satisfy the additional search condition were only inserted into the target table.

Using MERGE to delete the rows in the target table.

We can use WHEN NOT MATCHED BY SOURCE clause in SQL Server MERGE statement to delete the rows in the target table that does not match join condition with a source table.

For example, the row with locationID =2 in the target table does not match the join condition and the row is present only in the target table. So, when we use WHEN NOT MATCHED BY SOURCE and can either delete the row or update it. Please refer to the below T-SQL script to delete the row in the target table using WHEN NOT MATCHED BY SOURCE clause.

We cannot use WHEN NOT MATCHED BY SOURCE clause more than two times. If WHEN NOT MATCHED BY SOURCE clause in SQL Server MERGE statement was specified two times, one must use an update operation and another one must use delete operation. Please refer to below T-SQL script for using WHEN NOT MATCHED BY SOURCE clause two times.

We can use all the three clauses in the single merge statement to synchronize the target table with the source table. Please refer to the sample T-SQL script to synchronize the target table with the source table using MERGE statement and all the three clauses.

The TOP clause in MERGE

A TOP clause in the MERGE statement is used to limit the number of rows affected. A TOP clause is applied after removing all the rows that do not qualify for the insert, update, and delete operations. Please refer to below T-SQL script for the SQL Server MERGE statement with the TOP clause.

We can see only the row with LocationID =1 is updated and the rows that were not matched by source are not deleted and the rows that were not matched by the target are not inserted.

OUTPUT clause in MERGE

OUTPUT clause in the merge statement will return one row for each row that is modified in the target table. $action is used to know whether the row in the target table is deleted, inserted, or updated. Following T-SQL script is the example of the OUTPUT clause in the MERGE statement.

Please refer to the below image for the output of the above script which shows action and the inserted and deleted data in SQL Server MERGE statement. you can also insert the output data in another table if you want to track or audit changes later.

Using MERGE on a table with triggers

If we are creating instead of triggers on the target table, we must create instead of triggers for all the DML actions specified in the MERGE statement. If we create only INSTEAD OF INSERT trigger on the target table and specify INSERT, DELETE AND UPDATE operation in the MERGE statement, it throws an error.

Following is the example T-SQL script with INSTEAD OF INSERT trigger on the target table and all three DML operations in the MERGE statement.

Please refer to below example of instead of trigger for all actions specified in the merge statement.

If you have any trigger on the target table, you cannot use the OUTPUT clause in SQL Server MERGE statement directly.

In this case, we must create a table and use INSERT INTO to insert the output data into the newly created table.

Please refer to the below example for the trigger on the target table and output clause in the MERGE statement.

Alternatively, there are different ways to sync the source and target table. Let us see an example by using a left outer join to insert, delete rows and inner join to update matched rows. But we must write three different statements for synchronizing the tables.

Please refer to below T-SQL script which uses left outer join to delete rows present only in the target table.

Use inner join to update rows that match the join condition.

To insert new rows which are present only in source table use left outer join as below.

A word of caution

Please make sure you have proper indexes on both tables and join only the required columns so that you do not run into performance issues while synchronizing tables.

Conclusion

We explored different aspects of SQL Server Merge Statement with several examples in this article. In case you have any question, please feel free to ask in the comment section below.