question

user-490 (google) avatar image
user-490 (google) asked

Export table data for version control

I am keeping all of our database structures and selected table contents in subversion (but it could be any SCM) so that we can track changes. Right now, I am running a C# program (ScriptDB) to capture the structures and using BCP for most tables. This is working very well - we have daily snapshots of our important DB objects.

There is one table with only 800 records with 90+ columns from a legacy process where each record will have just a few changes made during the day. Unfortunately, the BCP export and a subsequent svn commit will identify all rows changed (correctly) but the difference reports don't yield a very understandable display (using WinMerge for visual differences).

My thoughts are to export this table as a set of Key-Value Pairs (KVP), prepending the primary key value to each output row: "pk#,column_name,column_value". That way I can easily see which field was changed for a given input record. A tool like svn blame would be very useful also.

Does anyone have any ideas for how to generate this output in SQL (sp, udfs, etc.) preferably without using cursors?

Thanks in advance.

auditexport-data
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

You could export

            
SELECT MyKey, 'Col1', MyCol1            
FROM MyTable            
UNION ALL            
SELECT MyKey, 'Col2', MyCol2            
FROM MyTable            

provided the datatype for MyCol1 and MyCol2 is the same (if you are only going to store the output in SVN you could presumably CAST the [MyColX] data to VARCHAR(8000) except where it is TEXT of VARCHAR(MAX)

FWIW We store database data versioning by using a TRIGGER on UPDATE/DELETE that stores the "old" row in an "Archive" table - same columns / structure as the main table, but with an extra AuditDate column and an indicator for whether it was an Update or a Delete.

This has the benefit of showing every change, rather than just the "state" at your daily export so, for example, would catch a fraud where a record was changed, and then changed back again, which your approach would miss.

Horses-for-courses though ...

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TG avatar image
TG answered

I think what Kristen said about triggers is the way to go for auditing changes (or versioning). Another way to achieve that PK,KV structure is to use UNPIVOT:

;with yourTable (pk, col1, col2, col3)            
as            
(            
       select pk = 1, col1 = 'v1', col2 = 'v2', col3 = 3 union all            
       select 2, 'v2', 'v3', 3            
)            
            
select up.pk            
       ,k as [key]            
       ,v as [value]            
from   (            
       select pk            
              ,col1            
              ,col2            
              ,convert(varchar(2), col3) as col3             
              from yourTable            
       ) d            
unpivot (v for k in (col1, col2, col3)) up            
            
OUTPUT:            
pk          key              value            
----------- ----------------------            
1           col1             v1            
1           col2             v2            
1           col3             3            
2           col1             v2            
2           col2             v3            
2           col3             3            
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

FWIW I have built audit tables in the past that stored Key/Value pairs, and audit tables that store the whole row.

The triggers to store Key/Value pairs were huge (although we generated them mechanically, so it was only physical size, and run-time, that were the issues rather than human programming effort).

It was very difficult to report off the key/value pairs to actually see what changed when - it required pivoting all the key/value rows back together to be able to see what actually changed.

So my preference is to audit whole rows to a table, rather than key/value pairs. I can see that in SVN the key/value pairs would be easier to store etc., and maybe your data lends itself to only needing key/value pairs, so YMMV :)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.