Introduction to the merge keyword in SQL Server

Brief introduction

The merge keyword is a magical DML keyword. It was introduced in SQL Server 2008, and it can be insert,update,delete simple and a sentence. MSDN's explanation for the merge is very concise: "Inserts, updates, or deletes are performed on the target table based on the results of joins to 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. With this description, we can see that the merge is about manipulating data between two tables.

You can imagine a scenario where you need to use the merge:

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, in the era of SQL Server 2008 without the merge, based on the source table to the target table operations need to write several insert,update,delete. Using the merge, you just need to use a single statement. Let's take a look at the example below.

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

Figure 1. Create a test table and insert test data

Now let's write a simple merge statement, as shown in Figure 2.

Figure 2. A simple merge statement

The resulting results are shown in Figure 3.

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

The results of the final target table are shown in Figure 4.

Figure 4. Results of the final target table