Friday, 1 May 2015

Merge statement with TOP clause - SQL Server by serverku

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?

No comments:

Post a Comment

Please Use Good Leanguage