SQL Merge - Persist data

Question

I use SQL Server 2014.

In my procedure, I have a MERGE statement and I have a question about it.

My MERGE statement has simple following structure:

MERGE dbo.T1 AS tgt
USING (SELECT ...) AS src ON ...
WHEN MATCHED THEN
    UPDATE ...
WHEN NOT MATCHED THEN
   INSERT ...

OUTPUT inserted.MyColumn
INTO @NewTable (MyColumnValue);

Just like how it populates a table for all inserts, I also need it to populate another table for all updates too.

Is is possible, and if yes then would you please let me know how?

OUTPUT $action, inserted.* and deleted.* will be updated for your columns, you can use $action to just pull the records from the update. — Kevin Cook, Feb 26 '15 at 21:59

score 0 · Accepted Answer · edited May 23 '17 at 12:20

No, it's not possible to direct the results to two tables. See this question.

You can make the table wider and output both the inserted and deleted columns on the same row:

MERGE dbo.T1 AS tgt
USING (SELECT ...) AS src ON ...
WHEN MATCHED THEN
    UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...

OUTPUT $action, inserted.col1, inserted.col2, deleted.col1, deleted.col2
INTO @NewTable (action, inserted_col1, inserted_col2, deleted_col1, deleted_col2);

Then you can split @NewTable however you want.

SQL Merge - Persist data

1 Answers1