Merge Output - Capture Column Name Updated

dawsonz 0
Viewed 27

I am merging a table and want to retain the previous value in a seperate table, along with the column that was updated.

I have got it working to retain the values, but want to know how I can retain the column name.

Existing table: tTable1


qID           qUnits           qDateTime        
1001          4900             2022-09-13 12:00:00.000             
1002          6800             2022-09-14 15:00:00.000
1003          7400             2022-09-14 13:00:00.000

Temp Table (holds updated values): #updateValues


qID           qOriginalUnit          qUpdatedUnit           
1001          4900                   8900                       
1002          6800                   13400            
1002          7400                   16500

The code i'm using currently, to output what the existing value was, before the update;

DECLARE @auditRecords TABLE(qID INT, PreviousValue VARCHAR(100)

MERGE tTable1 AS TARGET
USING #updateValues AS SOURCE
ON (SOURCE.qID = TARGET.qID)
WHEN MATCHED
THEN UPDATE SET
    TARGET.qUnits = SOURCE.qUpdatedUnit
OUTPUT SOURCE.qID, SOURCE.qOriginalUnit INTO @auditRecords(qID,PreviousValue);

I'd like to be able to include the column name that was updated, in this instance QUnits, so the output would look like the following;


qID           PreviousValue         UpdatedColumn
1001          4900                  qUnits
1002          6800                  qUnits
1003          7400                  qUnits
tsql sql-server sql
1 Answers

In this particular example, where you are only updating a single column, you can just hardcode the column name in the OUTPUT results:

OUTPUT SOURCE.qID,
       SOURCE.qOriginalUnit,
       'qUnits'
INTO @auditRecords(qID, PreviousValue, UpdatedColumn)

If you are updating multiple columns it becomes a bit more cumbersome as you need to compare values column by column.

You could also move this into an UPDATE TRIGGER in which you have access to the results of the COLUMNS_UPDATED function.

squillman0
Related