SQL Server MERGE

This article will give a detailed explanation of the MERGE statement in SQL Server. It was first introduced by Microsoft with the SQL Server version 2008. The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement, eliminating the need to write separate logic for each. It changes the data in a target table based on the data in a source table. We can also specify the condition on which we want to perform the MERGE statement for INSERT, UPDATE, or DELETE operations.

We can also say that this statement joins the required table with the target table and then executes necessary operations on them. This command provides better flexibility for modifying our complex SQL code and thus improves the readability of the code. It essentially updates an existing table using a key field comparison with the other table.

The below explanation helps to understand this concept easily:

Let's assume we have two tables, source & target tables, and we want to update the target table with the help of values stored in the source table. We can do this based on the following three possibilities:

1. Some rows in the "source table" are not found in the "target table." In this situation, it is required to add rows from the source to the target table.
2. Some rows in the "target table" are not found in the "source table." In this situation, it is required to delete rows from the target table.
3. Some rows in the "source and target table" have the same keys. However, they have distinct values in the non-key columns. In this case, it's necessary to update the rows in the "target table" with data from the "source table."

The "source & target table" and the corresponding insert, update, & delete actions are depicted in the diagram below:

Before introducing the merge statement, we must first generate three independent statements (INSERT, UPDATE, and DELETE) to update the data in the target table to match the rows in the source table. With the MERGE statement, we can now conduct all of these processes in one statement.

Syntax

The following are the syntax that illustrates the MERGE statement in SQL Server:

In this syntax, we first write the "target and source table" name after the MERGE clause. Second, define the merge_condition (similar to the join_condition in the join clause) with the ON clause determining how the source table and target table rows are matched. Usually, we use the key columns (primary or unique key) to retrieve the matched records. The merge_condition evaluates the results in three forms: MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE.

MATCHED: It indicates the rows that match the merge criterion. They are depicted as blue in the image. In this form, we will update the fields in the "target table" with data given in the "source table."

NOT MATCHED: It indicates the rows that are not matched in both table tables (source table and target table). They are depicted as orange in the image. In this form, the rows from the source table must be added to the target table. This form is often referred to as NOT MATCHED BY TARGET.

NOT MATCHED BY SOURCE: It indicates the records in the target table and no matching records found in the source table. They are depicted as green in the image. In this form, we will delete records from the target table when we want to synchronize the target table with the source table data.

MERGE Statement Example

Let us understand how the MERGE statement is used in the SQL Server and implement them practically. Here, we are going to create a simple table named "Products" and "TargetProducts" and add some rows to them.

Execute the following scripts to create both tables:

Table: Products

Table: TargetProducts

We can verify the above table by executing the SELECT statement as below:

It will return the following table data:

Now we will perform the MERGE command and try to make both tables synchronize with each other. The first thing we are going to figure out is how to manage the INSERT operation. We can execute the following code to merge the new data from the source to the target table:

Executing the code will return the following output:

In this output image, we can see that the two records with product IDs 3 and 4 that were not previously available in the target database have been added. This action is carried out by comparing the product id column of the source and destination tables.

Update Operation

After learning the MERGE query to insert records in the table, we will see how to use the same statement for updating values. It is required to have a field with a common value in both tables for updating the values. The database engine can only be able to match the records and to perform updation; the update operation on the given columns is used.

Here is the code script:

Executing the code will return the following output:

In the above output image, we can see that the initial value for the product "Desk" in the target table contains the "150" value. When we execute the MERGE command, it will update all matched records with the same entry in the source table. Here, we can also see that the update code has been added after the insert command. It indicates that the merge statement can execute the insert and update operation in the same script.

Delete Operation

After learning the MERGE statement to insert and update records in the table, we will now see how to delete the records from the target table using the same statement.

Executing the script will return the following output:

In the above image, we can see that all records whose product id is 5 and 6 are removed from the target table because they are not present in the source table. In this approach, we have used a MERGE statement to manage complex business requirements in a very simple and effective method.

Logging MERGE Changes using OUTPUT

SQL Server also allows us to log any changes by MERGE statement using the OUTPUT clause. Therefore, if we want to summarize all the operations that the MERGE statement has executed, it is required to edit the existing code to include the following output actions. The below code will produce the list of records on which the merge was performed, as well as the operation that was executed on each record.

Executing the above statement will produce the output actions performed by the merge statement:

Important points regarding MERGE Statement in SQL Server

Although we have known everything about the MERGE statement from the start and how to change the code to include logic for inserts, updates, and deletes operations, there are a few other essential considerations to bear in mind while preparing the merge code. These considerations are given below:

• A semi-colon should be used at the end of the MERGE statement. If we do not put the semi-colon, an error will be thrown.
• After writing the MERGE statement, we can also use the SELECT @@RowCount to see the number of records updated.
• At least one MATCHED clause needs to specify for the MERGE statement to get the result.
• It is mandatory to have SELECT permission on the source table and INSERT, UPDATE, and DELETE permission on the target table for performing the MERGE statement.
• SQL Server does not allow us to update the same variable twice in the same matched clause.

Conclusion

In this post, we looked at several aspects of the Merge Statement using multiple examples. This statement improves performance because all data is read and processed only once. When the data in both the source and destination tables were analyzed, earlier versions required three separate statements to do three different actions such as INSERT, UPDATE, or DELETE.