How MERGE statement works

Generating new ID without Identity

           Let’s see how Merge statement works, either it will read all the data from the source and start doing operation on the target or will it take row by row from the source and join with target and then update /insert the records in the target table.

  Here we are trying to address small misconception about merge, suppose if you have one table test1 in that one column is there called id we want to increase the values of the ID, but this column is not Identity. Let’s see this practically.

USE TestDatabase
GO
-- drop table test2
-- drop table test1

CREATE TABLE test1 (id INT, name VARCHAR(100))
GO
CREATE TABLE test2 (id INT identity, name VARCHAR(100))
GO
INSERT INTO test2(name) values ('amit'),('sumit'),('abhi'),('sachine'),('subhash')

INSERT INTO test1 VALUES(1, 'vikas');
INSERT INTO test1 VALUES(2, 'pnm');
SELECT * FROM test1;

-- drop table #t
CREATE TABLE #T (ID INT ,ACTION1 VARCHAR(100))

--SET SHOWPLAN_TEXT on
; MERGE test1 AS TARGET
using( SELECT id AS ID,NAME FROM test2 )
AS SOURCE
ON TARGET.ID=SOURCE.ID
WHEN MATCHED THEN
UPDATE
SET NAME=SOURCE.NAME
WHEN NOT MATCHED THEN
INSERT
VALUES((SELECT MAX(ID)+1 FROM test1),SOURCE.NAME)
OUTPUT INSERTED.ID,$ACTION INTO #T
;

--SET SHOWPLAN_TEXT OFF 
 SELECT * FROM test1

Now we are getting same values for the id this should 3, 4, 5 but it is displaying 3 for all three records. Let’s understand the execution process. In below execution plan we clearly see that’s it is reading data from target table and calculating Max after that it starting merge statement. So there is no chance to get the new max after every insert. This is the default behavior of the SQL server.

|--Stream Aggregate(DEFINE:([Expr1012]=MAX([TestDatabase].[dbo].[test1].[id])))
                                          |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1]))

-------------------------


  |--Table Merge(OBJECT:([TestDatabase].[dbo].[test1] AS [TARGET]), SET:(Insert, [TestDatabase].[dbo].[test1].[name]
  as [TARGET].[name] = [Expr1008],[TestDatabase].[dbo].[test1].[id] as [TARGET].[id] = [Expr1014]), SET
 :(Update, [TestDatabase].[dbo].[test1].[name] as [TARGET].[name] = [Expr1008]) ACTION:([Action1007]))
       |--Assert(WHERE:(CASE WHEN [Expr1020]>(1) THEN (0) ELSE NULL END))
            |--Sequence Project(DEFINE:([Expr1020]=conditional_row_number))
                 |--Segment
                      |--Sort(ORDER BY:([Bmk1003] ASC))
                           |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Action1007]=(4) THEN [Expr1012]+(1)
    ELSE [TestDatabase].[dbo].[test1].[id] as [TARGET].[id] END))
                                |--Nested Loops(Inner Join, PASSTHRU:(IsFalseOrNull [Action1007]=(4)))
                                     |--Compute Scalar(DEFINE:([Expr1008]=[TestDatabase].[dbo].[test2].[name]))
                                     |    |--Compute Scalar(DEFINE:([Action1007]=ForceOrder(CASE WHEN [TrgPrb1005] IS NOT NULL
      THEN (1) ELSE (4) END)))
                                     |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TestDatabase].[dbo].[test2].[id]))
                                     |              |--Table Scan(OBJECT:([TestDatabase].[dbo].[test2]))
                                     |              |--Compute Scalar(DEFINE:([TrgPrb1005]=(1)))
                                     |                   |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1] AS [TARGET]),
         WHERE:([TestDatabase].[dbo].[test1].[id]
        as [TARGET].[id]=[TestDatabase].[dbo].[test2].[id]) ORDERED)
                                     |--Stream Aggregate(DEFINE:([Expr1012]=MAX([TestDatabase].[dbo].[test1].[id])))
                                          |--Table Scan(OBJECT:([TestDatabase].[dbo].[test1]))

  As a solution you can use the SEQUENCE , CREATE SEQUENCE Test.CountBy1     START WITH 1     INCREMENT BY 1 ;

GO