SQL Server Merge - Getting matched records to another temp table
13,625
You could create a MATCHED
clause that does not change anything and just updates a variable, e.g.
DECLARE @T1 TABLE (A INT, B INT);
DECLARE @T2 TABLE (A INT, B INT);
DECLARE @T3 TABLE (Action VARCHAR(20), A INT, B INT);
INSERT @T1 VALUES (1, 1), (2, 2), (3, 3);
INSERT @T2 VALUES (1, 0), (2, NULL), (4, 0);
DECLARE @I INT; -- VARIABLE TO UPDATE
MERGE @T2 B
USING @T1 A
ON A.A = B.A
WHEN MATCHED THEN
UPDATE SET @I = 1 -- DO NOTHING MEANINGFUL IN THE UPDATE;
WHEN NOT MATCHED BY TARGET THEN
INSERT (A, B) VALUES (A.A, A.B)
OUTPUT $action, ISNULL(inserted.A, deleted.A), ISNULL(inserted.B, deleted.B) INTO @T3;
SELECT *
FROM @T3;
Will return:
Action A B
INSERT 3 3
UPDATE 1 0
UPDATE 2 NULL
So if you add a new column to @TmpTable
to store the action you can get your matched rows using:
SELECT *
FROM @TmpTable
WHERE Action = 'UPDATE';
And your new rows using:
SELECT *
FROM @TmpTable
WHERE Action = 'INSERT';
Author by
Naresh
Updated on June 07, 2022Comments
-
Naresh almost 2 years
I have a
MERGE
query to update data. In case of no match I am inserting records to source getting the output to a temporary table.Would it be possible to get the matched records to temporary table as well? Basically to avoid duplication of data in further processing I need to have copy of matched records.
This is my
MERGE
command:MERGE Product.ProductHeaderRepository AS t USING (SELECT GETDATE() as d, c1, c2, c3, Name FROM Supplier.ProductHeaderImport WHERE (BatchID = @BatchID) ) AS s ON dbo.GetProductHeaderId(s.c1,S.c2,S.c3) <0 WHEN NOT MATCHED BY TARGET THEN INSERT (Name, c1,c2,c3) VALUES (Name, c2,c2,c3) OUTPUT INSERTED.iD, s.c1, s.c2, s.c3 INTO @TmpTable;