![]() The first thing I do is to change that table variable into a temp table. Set based is 90% of the time the right way to go but how do we do set based solutions on the same table? New JOIN Way ON new = way Hmmm it also comes across as very RBAR doesn’t it? Every time we want to include another column to the change tracking, we have to add them row by agonizing row. The script is already big enough, if we keep adding more, it will get massive! The main issue that I was asked about was every time a column was deemed important and needed to be added to the list, they had to insert another INSERT INTO they thought that it wasn’t sustainable in the long run. Hey! It’s legacy code, let’s focus on just 1 problem at at time! 1 for each column we want to captureįROM ISNULL(OldColumn1, NewColumn1) NewColumn1 įROM ISNULL(OldColumn2, NewColumn2) NewColumn2 įROM ISNULL(OldColumn3, NewColumn3) NewColumn3 įROM ISNULL(OldColumn4, NewColumn4) NewColumn4 įROM ISNULL(OldColumn5, NewColumn5) NewColumn5 įROM ISNULL(OldColumn6, NewColumn6) NewColumn6 įROM XML PATH( 'Change'), ROOT( 'Changes') Actual update, outputting the old and new values You know the drill by now, I quite like to play along so let us facilitate that ( from now on I’m going to use Gist, formatting with native WordPress is starting to annoy me). Something to do with DRYness? Create Table: The question was, every time that they needed to add a column to a table, and change log it, they had to add multiple lines to the change tracking procedure and the procedure was getting gross and hard to maintain. Is that what I would do? Doesn’t matter, it was there before I got there, seems to work, and is low down on the list of priorities to change. What we had was INSERT statements, directly after a MERGE statement, that inserted into a table variable a hard-coded name of the column, the old value, and the new value. What we did have was “manual logging” and no, I’m not even talking about Triggers. We didn’t have Change Data Capture ( CDC), or Temporal Tables enabled ( have you seen the YouTube videos by Bert Wagner ( blog | twitter ) on these?). They’re good guys, I guess, they keep me on my toes. This is yet another time that a blog post has come about from a question by a developer.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |