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