You are here:Home » tsql » Merge statement and identity insert - SQL Server

Merge statement and identity insert - SQL Server

Today I asked by my friend for merge statement and identity insert, how to insert identity column data using merge statement? I posted for the merge statement without identity insert. Please read that post first before move ahead. So I would like to publish my friend’s question and answer too. It’s nothing but simple as identity insert for single table without merge. Let me generate objects required for the demo,
IF ( Object_id('EmpList1', 'U') > 0 ) 
  DROP TABLE emplist1 

IF ( Object_id('EmpList2', 'U') > 0 ) 
  DROP TABLE emplist2 

CREATE TABLE emplist1 
  ( 
     seq1     INT NOT NULL IDENTITY(1, 1), 
     empid1   INT NOT NULL PRIMARY KEY, 
     empname1 VARCHAR(50) 
  ) 

CREATE TABLE emplist2 
  ( 
     seq2     INT NOT NULL IDENTITY(1, 1), 
     empid2   INT NOT NULL PRIMARY KEY, 
     empname2 VARCHAR(50) 
  ) 

INSERT INTO emplist1 
VALUES      (1001, 
             'Emp1001') 

INSERT INTO emplist2 
VALUES      (1001, 
             'Emp2001') 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

DELETE FROM emplist2 
WHERE  seq2 = 2 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

SELECT * 
FROM   emplist1 

SELECT * 
FROM   emplist2 


You can see in the script and the image where we have to update and insert record in table emplist1 from emplist2, where record with seq1 will be updated and record with seq3 will be inserted with an identity. So emplist1 will become a target and emplist2 will become a source for this operation. Let me put a script here for same,
SET IDENTITY_INSERT emplist1 ON 

MERGE emplist1 
USING emplist2 
ON ( empid1 = empid2 ) 
WHEN matched THEN 
  UPDATE SET empname1 = empname2 
WHEN NOT matched BY target THEN 
  INSERT(seq1, 
         empid1, 
         empname1) 
  VALUES(seq2, 
         empid2, 
         empname2) 
WHEN NOT matched BY source THEN 
  DELETE; 

SET IDENTITY_INSERT emplist1 OFF 
You can see I used IDENTITY_INSERT on top and an identity column in the code while inserting records. Now checking records after the end,


I know you know about this, but I shared this post because I never used merge statement and identity insert at once. Have you ever used?