SQL Server – MERGE and ROW_NUMBER()

Hello Geeks, Hope you are all doing good.

First of all, Let me introduce myself to you.My name is Latheesh NK. About me, I am a very passionate SQL Server Developer, who loves scripting and performance tuning activities.

I would like to take this opportunity to thank Amit Bansal to provide me a great platform to share my experiences with SQL Server with you all.I hope, you all will be enjoying and sharing your thoughts as well here.

Recently, I had a request from one of my colleague who was looking for some option to generate a sequential number using MERGE and ROW_NUMBER.

Ok, let me explain the details on the subject. He is having a table with primary key which is an identity Key. He usually load data to this table from other table with a simple MERGE statement as below.

CREATE TABLE t11 (ID INT identity(1,1) primary key,NAME VARCHAR(50))
INSERT INTO t11 Values ('AAA'),('BBB'),('CCC')

CREATE TABLE t2 (ID INT,NAME VARCHAR(50))
INSERT INTO t2 values(4,'ZZZ'),(5,'YYY'),(3,'dfgdfg'),(2,'CC'),(12,'aqwe'),(1,'aqwe')

--MERGE to update/Insert the records
MERGE  t11   AS [Target]
            USING  t2 as source
            ON Target.id = Source.id    
            WHEN MATCHED THEN 
                 UPDATE SET Name = Source.Name 
            WHEN NOT MATCHED BY TARGET
            THEN INSERT( Name ) Values(Source.Name);

Select * From t11                  
--Clean up the objects
Drop table t11,t2

Now, there is a requirement from his client to avoid identity key from the original table(Do not ask me why?,sometimes we dont get the real reason 🙂 ). However, my colleague was looking for MERGE statement to accomodate his needs. Here is one of my try:

CREATE TABLE t11 (ID INT primary key,NAME VARCHAR(50))
INSERT INTO t11 Values (1,'AAA'),(2,'BBB'),(3,'CCC')

CREATE TABLE t2 (ID INT,NAME VARCHAR(50))
INSERT INTO t2 values(4,'ZZZ'),(5,'YYY'),(3,'dfgdfg'),(2,'CC'),(12,'aqwe'),(1,'aqwe')

MERGE  t11   AS [Target]
            USING  (Select Row_Number()Over(Order by T11.Id asc) Rn,
            (SELECT MAX(ID) FROM t11) as maxid,
            t2.id,t2.name From t2
            Left Join T11 On t2.ID = T11.ID ) as source
            ON Target.id = Source.id    
            WHEN MATCHED THEN 
                 UPDATE SET Name = Source.Name 
            WHEN NOT MATCHED BY TARGET
            THEN INSERT(
                  ID,
                  Name
                  ) Values(
                   maxid+ Rn,Source.Name);

Select * From t11                  

Drop table t11,t2

Hope, you enjoy the script. I love to hear from you for any other ways, lets have learning and sharing…

Regards, Latheesh NK

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “SQL Server – MERGE and ROW_NUMBER()”

Leave a Reply

Your email address will not be published. Required fields are marked *