Browse DevX
Sign up for e-mail newsletters from DevX


All the Changes, All the Time: Part II

In this 10-Minute Solution, Joe Lax demonstrates how to use Dynamic SQL to process those changes and apply them to the second database.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

o briefly recap the situation I described in "All the Changes, All the Time: Part I", my assignment was to capture all the changes being made in one database and, upon approval by an administrator, apply these changes to another copy of the database in production. In Part I, I described how I created triggers to write an audit trail of all changes to a central table. In this 10-Minute Solution, I demonstrate how to use Dynamic SQL to process those changes and apply them to the second database.

What Is Dynamic SQL?
Using SQL Server, you can populate a string with a proper SQL statement and then tell SQL Server to parse and execute the string. You can therefore dynamically change the command being passed to SQL Server by changing the value of the string. Following is a simple and rather useless snippet of code that will nevertheless illustrate this point before I go on to more complicated examples:

use pubs go create procedure dynamic_demo ( @tablename varchar(50) ) as exec ('select * from ' + @tablename) go dynamic_demo authors dynamic_demo

SQL Server will dynamically return a different result set based on the parameter you pass to the procedure.

The Central Audit Table
Table 1 shows the fields in the audit log table that are relevant to this discussion. In order to move the changes stored in this table to production, I need to traverse each row and—depending on the type of change—dynamically build either an insert, update, or delete statement. It's therefore natural to use a cursor. In the databases in question, the primary key (IID) for a table is never updated. Therefore, an update or delete statement can easily be formatted. For example, an update statement for any given update change would be "update table [table_name] set [column_name] = [new_value] where iid = [iid]" (items in [] are names of columns in the audit table).

Table 1. Audit Log Table Fields






Auto-generated primary key



Date the change was made



ID of company whose information was changed



Primary key of row that was changed (all tables in the database use a single integer column for the primary key)






Name of field that was changed (blank or 0 for inserted or deleted)



Name of table that was changed



Value before change



Value after change



0 for not moved to production database, 1 if moved

Here is the snippet of code that I'll use to build the update statement:

if @changetype = 'UPDATED' begin select @cmd = 'update ' + @dbdest + '..' + @tablename + char(13) + 'set ' + @fieldname + ' = new.' + @fieldname +char(13) + 'from ' + @dbsource + '..' + @tablename + ' new' +char(13) + 'where '+ char(13) + @dbdest + '..' + @tablename +'.iid = ' + cast(@rowid as varchar) + ' and ' + char(13) + 'new.iid = ' + cast(@rowid as varchar) end

Similarly, the delete statement is built with this code:

if @changetype = 'DELETED' begin select @cmd = 'delete from ' + @dbdest + '..' + @tablename + ' where iid ' + ' = ' + cast(@rowid as varchar) end

The insert statement is more complicated. It would be simpler if I could simply build an insert statement of this form:
    insert into productiondb.dbo.[table_name]
    select * from editdb.dbo.[table_name]
    where iid = [iid]
However, the edit database has additional fields on each table for auditing purposes that don't appear in the production database. So I need to specify each of the columns in my insert and select statement in order to eliminate the extra fields. I'll therefore create another stored procedure to create the insert statement by retrieving each of the columns for a given table from information_schema.columns. Following is the code I'll use:

create procedure sp_createinsert ( @dbdest varchar(50), @dbsource varchar(50), @tablename varchar(50), @rowid int, @cmd varchar(6000) output ) as set nocount on declare @column_name varchar(50), @insert varchar(2000), @select varchar(2000), @where varchar(500), @first int, @skip1 char(50), @skip2 char(50), @skip3 char(50) select @skip1 = 'changestatus', @skip2 = 'changetime', @skip3 = 'username' select @insert = 'insert into ' +@dbdest+ '..'+ @tablename + '( ' select @select = 'select ' select @where = 'where iid = ' + cast(@rowid as varchar) select @first = 1 declare mycolumns cursor static for select column_name from information_schema.columns where table_name = @tablename and column_name not in (@skip1,@skip2,@skip3) order by ordinal_position open mycolumns fetch next from mycolumns into @column_name while @@fetch_status = 0 begin if COLUMNPROPERTY(object_id(@tablename), @column_name, 'IsComputed') = 0 begin if @first = 1 — first column shouldn't have a comma before it begin select @insert = @insert + '[' + @column_name + '] ' select @select = @select + '[' + @column_name + '] ' select @first = 0 end else begin select @insert = @insert + ',[' + @column_name + '] ' select @select = @select + ',[' + @column_name + '] ' end end fetch next from mycolumns into @column_name end close mycolumns deallocate mycolumns select @insert = @insert + ')' select @select = @select + char(13) + ' from ' + @dbsource + '..' + @tablename select @cmd = @insert + char(13) + @select + char(13) + @where

In the end, for each line in the audit table, I generate the proper statement and store it in a variable named @cmd. With one line of code exec (@cmd) I am able to move the change to production.

You may not have the time to wade through the code given in these two 10-Minute Solutions. Hopefully you'll take away the following ideas:

  1. The metadata stored in the information_schema tables can be used in many situations to automate the creation of insert statements, trigger creation, and any other tasks you need done.
  2. Triggers are a very useful device for capturing audit changes.
  3. A central audit table can be used to store changes to all the tables in your database instead of creating another audit table for each.

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date