CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
TechDawg270
TechDawg270

Reputation: 725

SQL Server merge function - Delete and output deleted not working properly

I have a pretty standard script that uses the merge functionality to insert, update, and delete records. However, I am having a lot of trouble with outputting deleted results to an audit type table. When I run the procedure against a clean target table I somehow end up with all nulls in the audit table, despite the fact nothing has been deleted (only inserts to non-audit table at this point, which does happen as it should). Under the same conditions and against the same clean target table, if I remove the:

OUTPUT deleted.Column1, deleted.Column2 INTO Table_Audit (Column1, Column2)

and replace it with:

OUTPUT $action, Inserted.*, Deleted.*

I see that nothing has actually been deleted, which is odd considering the source and target tables were the same on both runs and the merge predicate remained the same. All the records in the action column of the second statement are inserts as we would expect. Literally the only thing different is what I just mentioned above. Any ideas as to what could be going on?

Upvotes: 3

Views: 2752

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Have a look at example K.

Put your merge statement in a subquery to a insert statement against your audit table. Make sure one of the columns is $action and filter the values to insert in the outer where clause to only insert deleted rows.

Upvotes: 3

Related Questions