Creating Automatic insert and update statements from SQL Tables using T-SQL

Shifting data around is a requirement for every developer and DBA.  Whether it is building (or rebuilding!) a database, migrating data for releases or just creating test data, I at least always seem to be opening a new query page and typing in 'insert into ....'.   I know that SQL Server 2005 has great little widgets for creating insert statements based off the table, but what I have always needed was a way to generate an update or insert statement based on data in an existing table in a different database.  Sometimes a DTS / BCP import/export job is too much work when there is only one or two rows, or maybe you can't get a file anywhere near the server because of security restrictions.  Maybe you just like doing things the hard way.

What you need is a T-Script which will read the contents of a table, then create update / insert statements for that data to go into the same table in a different server and database.

I've alway had little scripts written for specific tables, but on prompting from a friend, I decided to get serious and create a one-size-fits-all SQL generator which will generate a line of SQL for each row in a source table, which, when run on the target database, will perfectly replicate the data.  I know there are tools around to do this, but this is quick and very easy.  It also will generate either for a specific row of text, or simply for all rows in the source table.  Sorry no partial matching has been built in, but I'm sure a smart developer out there can easily tweak to introduce partial matching for rows.

Starting Out

The first port of call for a project like this is the system tables.  I've always loved the system tables, which are one of those weird circumstances where the structure of the product you are using is described by metadata within the product.  Like Reflection and other self-describing metadata, you can get a bit chicken-and-egg if you think about it for too long.

The first thing you need to do is get a list of the columns within the target table, and the data type of each of those columns.  This is done with a pretty simple piece of SQL:

select so.name, sc.name, st.name, sc.length
, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent
, ColOrder
from sysobjects so
inner join syscolumns sc
on so.id= sc.id inner join systypes st
on sc.xtype = st.xusertype where so.Name = 'SourceTableName'
Order by ColOrder

This will give you the list of columns within a source table, in the same order they are in the database.   This is the basic structure needed to get the data out.  The tricky column with the CASE statement checks the Status binary column and generates a Y/N depending on whether or not the column is an identity column - which we will come to later.

The problem with getting the information out is that we are working in looping sets - which is difficult to do in SQL Server (well, pre .NET anyway).  In order to collate the results needed, a temporary table will be created.  This temporary table will contain the actual output of the script.  Another temporary table is created as a staging point for the data which is in each column.

create table #output (Line varChar(4000), LineOrder int) -- Holds the output for the script
create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50)) -- Holds the values for each column/row combination

Working out the Algorithm

The basic algorithm in pseudo-code is:

for each column in the source table
   if the column is not an identity column
       insert into the #ColumnValues table the column name and the value from the source table
  end if
end for
for each row in the #ColumnValues table
   while each column in the table belonged to the same row in the source table
       concatenate the update/insert statement together into a string variable
   end while
   at the end of each column set for a row
   if an insert statement desired
     create the insert statement specific text
   else
     create the update statement specific text
   end if
end for
select all rows from the #Output table
 

This basic algorithm has some complicated parts when translated into T-SQL - the most conceptually difficult is selecting the values from a particular column in a table.  The problem is that you need dynamic SQL - no problem there - but you need to store the result into some type of variable.  You can't use locally declared T-SQL @ variables, because they are not in the scope of the dynamic SQL.  This is why a temporary table is used instead.    This can be used in the dynamic SQL, which looks like this:

exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)
select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType +
''' from ' + @tabName + ' order by ' + @SortCol + ' ' + '
declare @counter int
set @counter = 0 ' + '
update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10)
where ColName = ''' + @colName + '''' )

This rather scary looking piece of code does a couple of things.   The @colName variable is taken from the cursor loop, and is simply the name of the Column in the source table (as is @colType and @tabName).  The code works one column at a time across the table - like learning to read - left to right, down to the next row and on and on...  The insert statement takes the value read from the specific column of the source table and inserts the value read from the source table into the temp table (#columnValues).  The next line declares a counter and then incrementally assigns the RowNumber of the source table - this is to keep all the columns of the same row together - otherwise the results would be all over the place, with the values in different columns matched with the wrong primary keys - a bit like a board game accidentally bumped and all the pieces in the wrong place.  The last update statement uses the very oblique Set @value = Column = @Value + number syntax - a bit of a hidden secret of SQL Server which allows you to update a running counter for each row in a table.   You can either try and work out how it works or accept that it does and move on.

The row counter is then used a bit later on when creating the insert/update statement from the values in the #ColumnValues table.  It is used a loop switch so the code knows when the first column finishes and the next one ends.  This is because the structure of the original table :

row1 : col1 col2 col3 col4
row2 : col1 col2 col3 col4

is now represented in a table as :

row1 col1
row1 col2
row1 col3
row1 col4
row2 col1
row2 col2
etc....

The row counter (@RowNumber) is checked after each column in a post-loop check, to determine whether or not we have switched rows - ie, from row1 to row2.  At each row switch reading from the table, the insert/update statement is finished off and a new one is started.  Each time an insert/update statement is finished off, it is inserted into the results table.  When the script is finished, all of the statements are read from the temporary table, and the result will give you a nice list of insert statements which can then be executed into the server/database of your choice (providing, of course, that the exact same table definition exists in that database).

Putting it all together and getting output

To run the script, simply load it up in Query Editor or SQL Server manager.  Edit the @tab variable to the name of the source table, and type in 'INSERT' or 'UPDATE' into the @StatementType variable.  If you wish to do an Update statement or wish to do a single-row insert statement, put in the value of the Primary Key column for the table (currently this only works with a single column primary key) and the name of the column which is the primary key.   

The above example shows the script ready to run an all - rows set of insert statements for a table called 'PaymentStatusTest'.

This is the structure of the 'PaymentStatusTest' table:

Execute the script, and then copy out the results from the query results window into the location of your choice.  It helps to run Query Editor as 'text' output.

The code is tested on a narrow range of tables, and more esoteric column types may throw the code off.  Extra types (such as uniqueidentifier) can be programmed for by adding to the 'case' statement which determines what to do for each found column type.  This is primarily how to treat the column in the output script, and whether to have quotes around it or not.