Monday, March 04, 2013 6:27 AM
I’ve have to insert new rows to a table when the data changes for one or more columns. When a new row is inserted I need to record the before and after image by column e.g. on a Person table the surname and phone number changes then in the Audit table I need to insert two rows one for the change to surname and one for the change to phone number showing the before and after data.
Using a trigger or an OUTPUT clause I can quickly identify the rows that have changed but then it seems I need an INSERT statement to the Audit table for each COLUMN that has changed. Which could be quiet expensive if the number of columns is large.
The only alternative I could think of, but could NOT get to work, was to try to create a table with the changes and then UNPIVOT them in a single statement.
So my PIVOT table would be something like:
CREATE TABLE myPIVOT ( myKey INT, OldSurnameValue VARCHAR(255), NewSurnameValue VARCHAR(255), OldPhoneNumber VARCHAR(255), NewPhoneNumber VARCHAR(255) )
And then UNPIVOT that to an Audit table like:
CREATE TABLE Audit ( myKey INT, myColumnName VARCHAR(255), OldValue VARCHAR(255), NewValue VARCHAR(255) )My question is then can you use UNPIVOT in this way?
- Edited by Brendan Costigan Monday, March 04, 2013 6:28 AM
Monday, March 04, 2013 6:40 AMYou could use CDC to accomplish this. It would give that type of information, and then you could build a process to query the changes in the CDC tables.
- Marked As Answer by Brendan Costigan Monday, March 04, 2013 7:11 PM
Monday, March 04, 2013 3:22 PMModerator
SELECT myKey ,'SurnameValue' as myColumnName, OldSurnameValue as OldValue,NewSurnameValue as NewValue from myPIVOT UNION ALL SELECT myKey, 'PhoneNumber', OldPhoneNumber,OldPhoneNumber FROM myPIVOT Order by mykey,myColumnName
Monday, March 04, 2013 7:11 PM
I think I see how CDC could be used to address this problem, it is not something I have used before. Alas it is only available with the Enterprise Edition and we use a lesser version at work
Thanks for your help.
Monday, March 04, 2013 7:13 PM
Thanks for your reply but your solution still means I am doing a select for each column which is what I was trying to avoid.
Monday, March 04, 2013 8:12 PMModerator
SELECT myKey, Replace(myColumnNameOld,'Old','') as myColumnName, NewVal, OldVal from myPIVOT UNPIVOT (NewVal For myColumnNameNew IN ([NewSurnameValue], [NewPhoneNumber])) unpvtNew UNPIVOT (OldVal For myColumnNameOld IN ([OldSurnameValue], [OldPhoneNumber])) unpvtOld WHERE Replace(myColumnNameNew,'New','') = Replace(myColumnNameOld,'Old','') Order by mykey,myColumnName
Tuesday, March 05, 2013 4:58 AMIf you want to compare historical values, you could do some sort of UNPIVOT with your insert. I found a good dynamic UNPIVOT using XML and also using an exclusion table to filter out unwanted columns. Take a look at this post; I think I used something very similar to the last post there.