SQL Server uses the Merge keyword for table data synchronization

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

Figure 1: Creating a test table and inserting test data

Let's write a simple merge statement, shown in 2.

Figure 2. A simple merge statement

resulting in 3 of the results shown.

Figure 3. Update of the merge statement for the target table

The result of the final target table is shown in 4.

Figure 4. 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 the OUTPUT clause after the merge statement above, as shown in 5.

Figure 5. Merge statement followed by an output clause

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

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

Figure 7: Insert Merge statement only

We can also use the top keyword to limit the rows to which the target table is being manipulated, as shown in 8. With the top keyword added to the statement in Figure 2, we see that only two lines are updated.

Figure 8. 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, and add an extra limit of 9.

Figure 9. Merge statement with and restriction conditions added

Some limitations of the merge keyword

• Only one table can be updated with the merge keyword
• SQL Server 2008 or later
• Cannot have duplicate records in the source table

Microsoft MSDN's Merge Explanation:

Portal: Https://docs.microsoft.com/zh-cn/sql/t-sql/statements/merge-transact-sql

SQL Server uses the Merge keyword for table data synchronization