Merge into for SQL

Introduction

The merge keyword is a magic DML keyword. It is introduced in SQL Server 2008. It can combine insert, update, and delete into a simple sentence. Msdn's explanation of merge is very short and concise: "the target table is inserted, updated, or deleted based on the result of the connection to the source table. For example, you can insert, update, or delete rows in a table based on the differences found in another table. You can synchronize two tables .", Through this description, we can see that merge operates on the data between two tables.

It can be imagined that merge needs to be used in the following scenarios:

• Data Synchronization
• Data Conversion
• Insert, update, and delete operations on the target table based on the source table

 

Benefits of using the merge keyword

The first is a more short and refined statement. Before SQL Server 2008, there was no merge. to operate on the target table based on the source table, several insert, update, and delete statements must be written respectively. To use merge, you only need to use one statement. Here is an example.

First, create the source table and target table, and insert relevant data, as shown in 1.

Figure 1. Create a test table and insert Test Data

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

Figure 2. A simple merge statement

Result 3 is displayed.

Figure 3. Update the target table using the merge statement

 

Result 4 of the final target table is shown.

Figure 4. Final target table result

 

A powerful function of the merge statement is to use the output clause to output the data that has just been changed. Add the output clause after the preceding merge statement, as shown in Figure 5.

Figure 5. Add the output clause after the merge statement

 

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

Figure 6. Output data changes produced by the merge operation

 

Of course, the above merge keyword is followed by multiple when... The then Statement, which is optional. You can add or delete only the statement, as shown in 7.

Figure 7. Insert only the merge statement

 

We can also use the top keyword to limit the rows operated on the target table, as shown in figure 8. The top keyword is added to the statement in Figure 2. We can see that only two rows are updated.

Figure 8. Merge statement with the top keyword

 

However, matched constraints often cannot meet actual requirements. We can add and additional constraints on the statement in figure 7, as shown in figure 9.

Figure 9. Merge statement with and constraints added

 

Restrictions on merge keywords

• Only one table can be updated using the merge keyword.
• Duplicate records are not allowed in the source table.