August 7, 2017 at 5:20 am
I've created a SPROC to move a set of data spread over multiple tables from one database to another.
I'm using the MERGE function and writing dynamic SQL MERGE commands for each of the tables containing data I need to move.
So far, this works but I need to build a logging feature around this.
My ultimate goal is to build a reporting table that looks like this
Action TableName KeyValue ColumnName OldValue NewValue
INSERT MySettings 100 NEW RECORD
UPDATE MySettings 200 FirstName Bob Dave
UPDATE MySettings 200 LastName Smith Jones
INSERT MoreSettings 101 NEW RECORD
UPDATE MoreSettings 102 Colour Red Green
To get to this point, I first need to capture the MERGE output into a table which I will then dynamically unpivot into my desired format.
The crux of my issues is that while I can get my INSERTED and DELETED rows from MERGE into a table using OUTPUT INTO, INSERTED and DELETED are all on the same row but what I need for the INSERTED and DELETED values to be UNIONED together so that each column name is only specified once together with the Action type.
Can anybody help me to achieve this?
August 7, 2017 at 6:03 am
OUTPUT INSERTED and DELETED to a staging table then unpivot that table to the destination table
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply