0

I am making merge upserts to my data warehouse. I don't need to keep the history on this particular object, so I'm using a type i slowly changing dimension. I'm using OUTPUT $action so I can keep track of how many updates and inserts occurred per day.

My goal is to skip the logging step for updates where a particular column is updated (age, specifically), because I expect everyone who had a birthday to change ages (I can't include birthdate in the object for privacy). Counting those updates in the logging is undesirable.

So far, my logging is:

OUTPUT $action, 
CASE $action 
    WHEN 'UPDATE' THEN 'Found matching key: updated existing record' 
    WHEN 'INSERT' THEN 'No matching key: inserted new record'
     END 
INTO @SummaryOfChanges(Change, Reason)

I want to add logic that says if the column age is the column updated, then skip the logging step.

Unlike the inserted and deleted parts of $action, I don't see a way to call back info about the updated rows. I've tried a variety of CASE statements within the OUTPUT step, but don't understand how to check if age was one of the columns updated during the row merge. I can't seem to check if the source and target age match within the OUTPUT clause.

Why?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
henhen
  • 23
  • 9
  • You can use the [special tables `inserted` and `deleted`](http://www.sqlservercentral.com/articles/T-SQL/156901/). You can do something like `CASE WHEN inserted.age != deleted.age THEN 1 ELSE 0 END`. You may need to handle NULLs. – Steven Hibble Aug 06 '18 at 22:51
  • Thanks Steven - I found those commands for inserted and deleted, but not for updated, since I'm not inserting or removing these rows. Do you know if there is an `updated` special table? – henhen Aug 06 '18 at 23:16
  • 1
    `UPDATE` results in an old row deleted and then a new row inserted, so you should look at both `deleted` and `inserted` special tables. – Vladimir Baranov Aug 07 '18 at 01:36
  • I believe that the `UPDATE` portion of the merge command updates existing records instead of deleting or inserting. Auto-incrementing primary key of the rows updated do not change. It really does appear to be a standard update. Possible this info isn't captured in the special tables? – henhen Aug 07 '18 at 15:55
  • @henhen, Vladimir is correct. When it comes to the special tables, an update produces rows in both the `inserted` and `deleted` tables. There is no `updated` table. Output `$action, inserted.age, deleted.age` and run a test. – Steven Hibble Aug 07 '18 at 18:47
  • 1
    Thank you Vladimir, thank you Steven! You guys are great! And totally correct. Because my auto-increment key wasn't changing, and the syntax looks like a straight update, I was mistaken thinking those changes weren't logged to `UPDATED` or `DELETED`. Now I see them! First I check `CASE WHEN ISNULL(inserted.age,'') = ISNULL(deleted.age,'')`, then standard logging, else `NULL`. When inserting into my log, I just check for `WHERE Reason IS NOT NULL` . Works like a charm. – henhen Aug 08 '18 at 00:09

0 Answers0