How to write MERGE statement in sql server

Question

Post reply

How to write MERGE statement in sql server

kiran.rajenimbalkar

SSCrazy

Points: 2603

April 22, 2019 at 8:28 am

#3511933

Hi Sir,

How to write MERGE statement when using more then two source tables joining with the TARGET table.

Sample SQL DDL and insert statement is as below,

CREATE TABLE m_exp
(m_exp_key INT,
 m_dea_key INT,
 m_del_key INT,
 m_date_key INT
 )

INSERT INTO m_exp VALUES (10,13,2,201901);
INSERT INTO m_exp VALUES (10,13,2,201902);
INSERT INTO m_exp VALUES (10,13,2,201903);
INSERT INTO m_exp VALUES (20,14,2,201901);
INSERT INTO m_exp VALUES (20,14,2,201902);
INSERT INTO m_exp VALUES (20,14,2,201903);

CREATE TABLE m_exp_year
(mey_exp_key INT,
mey_id INT,
mey_year INT)

INSERT INTO m_exp_year VALUES (10,1000,2015);
INSERT INTO m_exp_year VALUES (20,2000,2017);
INSERT INTO m_exp_year VALUES (30,3000,2016);
INSERT INTO m_exp_year VALUES (40,4000,2018);
INSERT INTO m_exp_year VALUES (50,5000,2018);
INSERT INTO m_exp_year VALUES (60,6000,2019);

CREATE TABLE m_exp_dupli
(med_orig_key INT,
med_orig_id INT,
med_dupl_key INT,
mey_dupl_id INT
)

INSERT INTO m_exp_dupli VALUES (10,1000,40,4000);
INSERT INTO m_exp_dupli VALUES (20,2000,30,3000);
INSERT INTO m_exp_dupli VALUES (50,5000,60,6000);

Sample SQL's I have written

--DELETE duplicate records from m_exp


DELETE FROM zzz
FROM   m_exp zzz
       JOIN m_exp_year a ON m_exp_key = a.mey_exp_key
       JOIN m_exp_dupli ON A.mey_id = mey_dupl_id
       LEFT JOIN m_exp_year b ON med_orig_id = b.mey_id
WHERE  EXISTS
(
    SELECT 1
    FROM   m_exp a
    WHERE  (a.m_exp_key = b.mey_exp_key
             AND a.m_dea_key = zzz.m_dea_key
             AND a.m_del_key = zzz.m_del_key
             AND a.m_date_key = zzz.m_date_key));

--UPDATE duplicate keys with the original key in m_exp

UPDATE zzz
SET zzz.m_exp_key=B.mey_exp_key
FROM   m_exp zzz
       JOIN m_exp_year a ON m_exp_key = a.mey_exp_key
       JOIN m_exp_dupli ON a.mey_id = mey_dupl_id
       LEFT JOIN m_exp_year b ON med_orig_id = b.mey_id;

From above two delete and update statements, when I executed I was getting error "can not insert duplicate id's into table m_exp, So need to write MERGE statement instead of delete and update.

So,request you please suggest me for this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

This is a bit odd, when I run the code, I don't get any errors!

😎

Here is the code I ran (slightly modified to use temp tables)

USE TEEST;
GO
SET NOCOUNT ON;

CREATE TABLE #m_exp
(m_exp_key INT,
 m_dea_key INT,
 m_del_key INT,
 m_date_key INT
 )
INSERT INTO #m_exp VALUES (10,13,2,201901);
INSERT INTO #m_exp VALUES (10,13,2,201902);
INSERT INTO #m_exp VALUES (10,13,2,201903);
INSERT INTO #m_exp VALUES (20,14,2,201901);
INSERT INTO #m_exp VALUES (20,14,2,201902);
INSERT INTO #m_exp VALUES (20,14,2,201903);
CREATE TABLE #m_exp_year
(mey_exp_key INT,
mey_id INT,
mey_year INT)
INSERT INTO #m_exp_year VALUES (10,1000,2015);
INSERT INTO #m_exp_year VALUES (20,2000,2017);
INSERT INTO #m_exp_year VALUES (30,3000,2016);
INSERT INTO #m_exp_year VALUES (40,4000,2018);
INSERT INTO #m_exp_year VALUES (50,5000,2018);
INSERT INTO #m_exp_year VALUES (60,6000,2019);
CREATE TABLE #m_exp_dupli
(med_orig_key INT,
med_orig_id INT,
med_dupl_key INT,
mey_dupl_id INT
)
INSERT INTO #m_exp_dupli VALUES (10,1000,40,4000);
INSERT INTO #m_exp_dupli VALUES (20,2000,30,3000);
INSERT INTO #m_exp_dupli VALUES (50,5000,60,6000);
--Sample SQL’s I have written

--DELETE duplicate records from m_exp
DELETE FROM zzz
FROM   #m_exp zzz
       JOIN #m_exp_year a ON m_exp_key = a.mey_exp_key
       JOIN #m_exp_dupli ON A.mey_id = mey_dupl_id
       LEFT JOIN #m_exp_year b ON med_orig_id = b.mey_id
WHERE  EXISTS
(
    SELECT 1
    FROM   #m_exp a
    WHERE  (a.m_exp_key = b.mey_exp_key
             AND a.m_dea_key = zzz.m_dea_key
             AND a.m_del_key = zzz.m_del_key
             AND a.m_date_key = zzz.m_date_key));
--UPDATE duplicate keys with the original key in m_exp
UPDATE zzz
SET zzz.m_exp_key=B.mey_exp_key
FROM   #m_exp zzz
       JOIN #m_exp_year a ON m_exp_key = a.mey_exp_key
       JOIN #m_exp_dupli ON a.mey_id = mey_dupl_id
       LEFT JOIN #m_exp_year b ON med_orig_id = b.mey_id;
SELECT * FROM #m_exp;
SELECT * FROM #m_exp_year;
SELECT * FROM #m_exp_dupli;

DROP TABLE #m_exp;
DROP TABLE #m_exp_year;
DROP TABLE #m_exp_dupli;

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 2

Hi Sir,

Yes, this will not give an error as this is a sample sql but I do have another sql and need to use MERGE statement in it.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 3

Sorry, my bad! Can you post the DDLs with the constraints, the temp tables / your example does not have any constraints, hence no error.

😎

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 4

Hi Sir,

Just I would like to know that ,

I have to update the keys from main table but I joined more then two tables as a source.

So how to use more then tow source tables in USING clause in MERGE statement?

This is my sample update statement,

UPDATE zzz
SET zzz.m_exp_key=B.mey_exp_key
FROM   m_exp zzz
       JOIN m_exp_year a ON m_exp_key = a.mey_exp_key
       JOIN m_exp_dupli ON a.mey_id = mey_dupl_id
       LEFT JOIN m_exp_year b ON med_orig_id = b.mey_id;

Hope you can understand 🙂

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 5

Something is missing here, post the DDLs for the tables and do remember to include the constraints!

😎

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 6

Read this: https://www.sqlservercentral.com/articles/understanding-the-merge-dml-statement-in-sql-server-2008

And then this: https://www.sqlservercentral.com/articles/performance-of-the-sql-merge-vs-insertupdate