Merge Output statement [Combine Deleted.* with Inserted.*]
-
Hi All,
I've created a merge statement which Deletes, Inserts and Updates a table, this is working as intended.
I'm now attempting to get the Output results to write to a second table so I can record what the Deletes, Inserts and Updates.
Issue: I'm needing the output of all the Deleted, Inserted and Updated rows (Deleted.* will give me Deletes and Updates and Inserted.* will give me Inserts and Updates)
When I select all from Deleted.* an Inserted.* it inserts each row twice, however I can't choose just deleted as then I can't get the insert values, and I also can't choose just inserted as then I don't get the deleted values. is it possible to get the output of Deletes, Updates & Inserts without having it duplicate the update row, therefore adding each column twice? Please see screenshot below of what it appears like.
Query:
OUTPUT
$Action, deleted.*, inserted.*
Result
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
-
You're seeing the before (deleted.*) and after (inserted.*) picture. You can't do that with a single set of columns. What are you trying to do where this is causing problems?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
You can join Deleted & Inserted so you can include columns of both in one result set. Just make sure you have a unique key to join on.
There are some performance risks, especially if you are touching a lot of records at once.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply