You are here:Home » top » Merge statement with TOP clause - SQL Server

Merge statement with TOP clause - SQL Server

A week ago, I posted for Insert, Update and Delete statement with TOP clause and Merge statement as an individual post. If you haven't read those posts, then read it before to move next. In this post I used TOP clause with DML operations and Merge statement, but both are individual posts. Let me put these two posts together here and create new one.

What is it?
It is nothing but the form of two individual posts and it is Merge statement with TOP clause.   I never used Merge statement and the TOP clause at once. Let me create the required objects in this demo or we can pick from an earlier post,
-- Creating tables used for merge operation 
IF ( Object_id('UsingTable') > 0 ) 
  DROP TABLE usingtable 

CREATE TABLE usingtable 
  ( 
     refid INT IDENTITY(1, 1), 
     name  VARCHAR(100) 
  ) 
GO

IF ( Object_id('TargetTable') > 0 ) 
  DROP TABLE targettable 

CREATE TABLE targettable 
  ( 
     childid INT, 
     val     INT 
  ) 
GO

-- Inserting records in both tables 
INSERT INTO usingtable 
            (name) 
VALUES      ('Target-1'), 
            ('Target-2'), 
            ('Target-3'), 
            ('Target-4'), 
            ('Target-5') 

GO 

INSERT INTO targettable 
            (childid, 
             val) 
VALUES      (1,1), 
            (2,2), 
            (3,3), 
            (6,6) 
GO 
Now we will run merge statement with TOP clause and also view Target table’s data before and after script run,
SELECT * 
FROM   targettable 

MERGE TOP (2) targettable 
using usingtable 
ON ( refid = childid ) 
WHEN matched AND childid = 3 THEN 
  DELETE 
WHEN matched THEN 
  UPDATE SET val = val + 5 
WHEN NOT matched BY target THEN 
  INSERT(childid, 
         val) 
  VALUES(4, 
         4) 
WHEN NOT matched BY source THEN 
  DELETE; 

SELECT * 
FROM   targettable 

GO
Merge with TOP clause

Merge without TOP clause

You can see from both images, with Merge statement with TOP clause updated only 2 rows and remaining insert and delete operation not happened which happened with Merge statement without TOP clause. Did you used both at once?