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,
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,
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?
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 OFFYou 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?
Comments
Post a Comment