TSQL: Merge

• Introduced in SQL Server 2008
• Allows for consolidated approach for insert, update, delete operations
• Allows code efficiency but does not necessarily increase performance
• Minimum permission required: Select on source; Insert/Update/Delete on target

Sample:

First, I created 2 tables, and inserted 2 rows on temp1.
 create table temp1
(empid int identity(1,1),
empname varchar(30),
age int)

create table temp2
 (empid int identity(1,1),
 empname varchar(30),
 age int) insert into temp1(empname, age)
 values('jenny', 39),
('tom', 38) 
Content of my table temp1:

 

 

 

 

Temp2 is empty on purpose. 

Merge two tables: 

merge into temp2 as t
 using temp1 as s on t.empid = s.empid
 when matched then
update
       set t.empname = s.empname,
       t.age = s.age
 when not matched by target then
      insert(empid, empname, age)
      values(s.empid, s.empname, s.age)
 when not matched by source then
     delete;

Output:
Temp1 and Temp2 have the same set of records

Second, I updated one record to test if edited records would be updated, and

update temp1
 set age = 40
 where empid = 2

Content of temp1 and temp2 tables:

I ran the same merge script again, and got this resultset.

Third, I deleted one record in temp1, and ran merge, and temp2 got updated with what's in temp1.

Notes:

•  Set identity_insert to ON to the tables to avoid this kind of error:

 "Cannot insert explicit value for identity column in table <table name> when IDENTITY_INSERT is set to OFF."

 

•   DROP foreign key constraints to avoid this kind of error:

  

"The MERGE statement conflicted with the REFERENCE constraint <constraint name>. The conflict occurred in database <database name>, table <table name>, column <column name>. The statement has been terminated."

 

•  Recreate the constraints dropped earlier.

•  If you encountered this error, ensure that you have specified correct/complete join condition like in the sample below.

"The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows."


 merge into <target table name> as t
using <source table name> as s on t.org_id = s.org_id and t.role_id = s.role_id
when matched then
      update set t.role_id = s.role_id,
                       t.paid = s.paid,
                       t.disabled = s.disabled,
                       t.access = s.access
when not matched by target then
      insert(org_id,role_id,paid,disabled,access)
      values(s.org_id,s.role_id,s.paid,s.disabled,s.access)
when not matched by source then
         delete;