Use of merge in SQL Server

Many people know that there is a merge key in Oracle, and SQL Server actually supports merge from version 2008.

The update, Insert, and delete operations can also be done in a SQL sentence in the same way as Oracle.

Here is a simple example of a direct code,

Number of preparations:

IF object_id (' TestA ', ' u ') is not nulldrop table testagoif object_id (' Testb ', ' u ') are not nulldrop table Testbgocreate table TestA (ID int,title NVARCHAR ()) Gocreate TABLE testb (id int,title NVARCHAR ()) Goinsert into TestA (id,title) VALUES (1 , N ' a '), (2,n ' B '), (3,n ' C '), (4,n ' D '), (5,n ' E ') Goinsert into Testb (id,title) VALUES (1,n ' one '), (3,n ' three '), (5,n ' V '), (7,n ' seven ') , (9,n ' IX ') GO

Now the need is to use TESTB to update testa,id the same Update,id different insert

The normal method is two steps away:

UPDATE ASET a.title = b.titlefrom TestA ainner JOIN testb b on a.id = B.id;insert into TestA (id,title) SELECT A.id,a.titlef ROM Testb Awhere not EXISTS (SELECT * from TestA WHERE ID = a.id);

With the merge, it can be done in one sentence, and it's still high efficiency:

MERGE into TestA as ausing Testb as BON (a.id = b.id) when Matchedthen UPDATE SET a.title = B.titlewhen not matched by Targ Etthen INSERT (id,title) VALUES (b.id,b.title);

For more use of the merge, or please refer to MSDN: 1, references 2

Use of merge in SQL Server