SQL Server MERGE

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.

    can imagine a scene that needs to use the merge, such as:

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

Update operations for large amounts of data are recommended for use in this way

For a simple test:

--source table
CREATE TABLE test_from (id INT, Val VARCHAR (20));

--Target table
CREATE TABLE test_to (id INT, Val VARCHAR (20));

--Insert Source table
INSERT into Test_from VALUES (1, ' A ');
INSERT into Test_from VALUES (2, ' B ');

--merge source table to target table
MERGE test_to USING Test_from
On (test_to.id = test_from.id)--The condition is the same ID
When matched then update SET test_to.val = test_from.val--Match, update
When isn't matched then insert VALUES (test_from.id, Test_from.val)-source table has, target table not, insert
When not matched by SOURCE then DELETE; --The target table has, the source table is not, the target table that data is deleted.

--Check the target table data for the first time.
SELECT * from Test_to;

ID val
----------- --------------------
1 A
1 A

--Update the source table
UPDATE test_from SET val = ' A2 ' WHERE id = 1;
--Delete source table
DELETE from test_from WHERE id = 2;
--Insert Source table
INSERT into Test_from VALUES (3, ' C ');

--merge source table to target table
MERGE test_to USING Test_from
On (test_to.id = test_from.id)--The condition is the same ID
When matched then update SET test_to.val = test_from.val--Match, update
When isn't matched then insert VALUES (test_from.id, Test_from.val)-source table has, target table not, insert
When not matched by SOURCE then DELETE; --The target table has, the source table is not, the target table that data is deleted.

--Check the target table data again.
SELECT * from Test_to;

ID val
----------- --------------------
1 A2
3 C

The above example is tested under SQL Server Express.