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