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,
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?
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 operationNow we will run merge statement with TOP clause and also view Target table’s data before and after script run,
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
SELECT *Merge with TOP clause
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 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