The merge keyword in SQL Server

Introduction

The merge keyword is a magical DML keyword. It was introduced in SQL Server 2008, it can be insert,update,delete simple and as a sentence. MSDN's interpretation of the merge is very short: "Insert, update, or delete operations on the target table based on the results of the join with the source table." For example, you can synchronize two tables by inserting, updating, or deleting rows in a table based on differences found in another table. "By this description, we can see that the merge is about manipulating data between two tables.

Imagine a scenario where you need to use the merge, such as:

• Data synchronization
• Data conversion
• Insert,update,delete operation of target table based on source table

benefits of using the Merge keyword

The first is a more concise statement, before SQL Server 2008 without the merge era, based on the source table to the target table to write several insert,update,delete. With merge, you just need to use a single statement. Let's look at an example below.

First create the source and target tables, and insert the relevant data, as shown in the following code.

Create a test table and insert test data

--Creating a source tableCREATE TABLESourceTable (IDINT,[DESC] varchar( -))--Create a target tableCREATE TABLETargettable (IDINT,[DESC] varchar( -))--inserting data into a source tableINSERT  intoDbo. SourceTable (ID,[DESC])Values(1,'Description 1')INSERT  intoDbo. SourceTable (ID,[DESC])Values(2,'Description 2')INSERT  intoDbo. SourceTable (ID,[DESC])Values(3,'Description 3')INSERT  intoDbo. SourceTable (ID,[DESC])Values(4,'Description 4')--Insert data for the target tableINSERT  intoDbo. Targettable (ID,[DESC])Values(1,'exists in the source table and will be updated')INSERT  intoDbo. Targettable (ID,[DESC])Values(2,'exists in the source table and will be updated')INSERT  intoDbo. Targettable (ID,[DESC])Values(5,'does not exist in the source table and will be deleted')INSERT  intoDbo. Targettable (ID,[DESC])Values(6,'does not exist in the source table and will be deleted')Go

Let's write a simple merge statement, as shown in the following code.

A simple merge statement:

MERGE intoTargettable astusing SourceTable asS onT.id=s.id whenMatched--when the t.id=s.id on the back of the above, the data in the target table is updated Then UPDATE SETT.[DESC]=S.[DESC] when  notMatched--There is no ID in the target table, there is in the source table, then the relevant data is inserted Then INSERT VALUES(S.id,s.[DESC]) when  notMatched bySOURCE--exists in the target table and does not exist in the source table, delete Then DELETE;

The resulting results are as shown.

Update of the merge statement for the target table

The result of the final target table is as shown.

Results of the final target table

The merge statement also has a powerful function of outputting the data that has just been changed through the output clause. We add an OUTPUT clause after the above merge statement, as shown in.

Merge statement followed by an output clause

When the merge operation is complete, the changed statements are output, as shown in 6.

Data changes resulting from the output merge operation

Of course, the above merge keyword uses a number of when after ... Then statement, and this statement is optional. You can also just add or delete, as shown.

Just insert the merge statement

We can also use the top keyword to limit the rows to which the target table is manipulated, as shown in. With the top keyword added to the statement on the second piece of code, we see only two lines being updated.

Merge statement using the TOP keyword

But just matched this limitation is often not enough to meet the actual demand, we can add an additional constraint on the basis of the statement in Figure 7, as shown in.

Merge statement with and constraints added

Some limitations of the merge keyword

• Only one table can be updated with the merge keyword
• Cannot have duplicate records in the source table

Summary

This article simply explains the use of merge key words. If you are using a version of SQL Server 2008, discard if when facing some business such as inventory checkout ... else and handwriting Update,insert, using the merge keyword makes this kind of operation easier and more enjoyable.

The merge keyword in SQL Server