How to retrieve old values in OUTPUT clause with SQL MERGE statement

Question

I'm using MERGE statement to update a product table containing (Name="a", Description="desca"). My source table contains (Name="a", Description="newdesca") and I merge on the Name field.

In my Output clause, I would like to get back the field BEFORE the update -> Description = "desca".

I couldn't find a way to do that, I'm always getting back the new value ("newdesca"). Why?

Which version of SQL are you using? What does the query look like? What do you mean by "output clause"? — Tomas Pastircak
– Tomas Pastircak, Commented Apr 23, 2014 at 9:33

GarethD · Accepted Answer · 2014-04-23 09:48:11Z

7

Can you not just used the deleted memory-resident table. e.g:

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

CREATE TABLE #T (Name VARCHAR(5), Description VARCHAR(20));
INSERT #T (Name, Description)
VALUES ('a', 'desca'), ('b', 'delete');

MERGE #T AS t
USING (VALUES ('a', 'newdesca'), ('c', 'insert')) AS m (Name, Description)
    ON t.Name = m.Name
WHEN MATCHED THEN 
    UPDATE SET Description = m.Description
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Name, Description)
    VALUES (m.Name, m.Description)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $Action, inserted.*, deleted.*;

IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL
    DROP TABLE #T;

The output of this would be:

$Action | Name  | Description | Name | Description
--------+-------+-------------+------+--------------
INSERT  |   c   |   insert    | NULL | NULL
UPDATE  |   a   |  newdesca   | a    | desca
DELETE  | NULL  |    NULL     | b    | delete

edited Apr 23, 2014 at 9:48

answered Apr 23, 2014 at 9:43

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user957479 Over a year ago

Thanks, that was the idea ! I just had another problem (I wanted a singled "description" column filled correctly with old value in case of update and new value in case of insert) but I solved it easily with a CASE on the $Action column.

Collectives™ on Stack Overflow

How to retrieve old values in OUTPUT clause with SQL MERGE statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related