Different OUTPUT when using MERGE statement?

4
votes

I'm using MERGE to insert or update records into a table:

MERGE INTO target 
USING SELECT * FROM @source
WHEN MATCHED THEN
UPDATE SET ...columns...
WHEN NOT MATCHED THEN
INSERT ...columns...
OUTPUT inserted.* INTO @insertedRecord

If the above statement performs an update, does the updated record be inserted into the table variable?

2
sqlsql-server

2 Answers

3
votes

Yes.

The inserted psuedotable will handle the new values that go into the table, whether inserted or updated.

The deleted pseudotable will handle the old values, whether deleted or updated (for updates, it'd be the previous value).

Even neater: you can reference the source table in a merge as well, e.g. OUTPUT @source.* (although you may need an alias on that for it to work).

2
votes

$action will give you the action type.

inserted.* will give you the new values from updates and values for inserted rows.

deleted.* will give you old values for updates and values for deleted rows.

You might want to push this into a temp table 

OUTPUT $action, inserted.*, deleted.*, @source.* into #changes

and work with that to get the results you are looking for.