Sql-server – MERGE with OUTPUT doesn’t seem to be doing the right thing

mergesql serversql-server-2008

I'm adding a foreign key to a table, and removing any rows that violate the FK, copying them into a ModifiedTable_invalid table. As part of the script, I've got the following MERGE command:

MERGE ModifiedTable t1
USING TargetTable tt
ON t1.JoinColumn = tt.JoinColumn
WHEN MATCHED THEN
    UPDATE SET t1.FkColumn = tt.FkSource
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT DELETED.* INTO ModifiedTable_invalid;

However, this command seems to be inserting EVERY row from ModifiedTable into ModifiedTable_invalid, not just those deleted by the MERGE command. What's going on, and how do I get it to only put the deleted rows in ModifiedTable_invalid?

Best Answer

When you update a row, it will appear in both the inserted (post-update value) and deleted (pre-update value) pseudo-tables. If this seems odd, consider that an update is logically a deletion followed by an insert (though the update may not be physically performed that way).

When used with MERGE, the OUTPUT clause can include an extra column named $action. Adding this column to your query will show which action was taken ('INSERT', 'UPDATE', or 'DELETE') for each row.

For example:

insert into ModifiedTable_invalid(Id /* And other columns */)
select
    Id
    /* And other columns */
from
(
    merge ModifiedTable t1
    using TargetTable t2 on t1.JoinColumn = t2.JoinColumn
    when matched then update set t1.FkColumn = t2.FkSource
    when not matched by source then delete
    output 
        $action as DMLAction,
        deleted.Id as Id /* And other columns... */
) outputs
where
    DMLAction = 'DELETE';

Updated rows will have $action = 'UPDATE'.

Also see Adam Machanic's post on using OUTPUT with the MERGE statement for some other nice examples.

Related Question