Mark Clerget's Blog

Okay, so I'm hyping a bit. So give me a break...this little gem took me a few days to write and weeks to refine in the 90's. Anyway, I updated it to take advantage of the "dynamic aggregator" approach shown in my previous query.

So you are probably saying, "Great, so you can generate a CREATE TABLE statement...Whoopee!". Well, if I could help you fill that table or tables using a script that dynamically generates INSERT statements based on the contents of a table, would that be worth something to you? Great! Read further and lets really impress those SQL jockey friends of yours.

Problem to Solve
Write a query that dynamically generates a series of INSRET statements based on the contents of a table. The output of this query can then be used to fill an empty table or replace a table. It also accomodates preserving IDENTITY columns.

Goals
Like always, I try to make my SQL tools reusable and tight. In this query, I opt to blend simplicity (one cursor for the tables) with the powerful "dynamic aggregator" shown in my last blog (for the columns). So without further delay....

Solution to the Problem
Paste the following query into a SQL Server Query Analyzer window and set the current database to the desired database that you want to generate the INSERT script for. Change the WHERE clause of the DECLARE CURSOR query to reflect one or more tables from that database. Set the output format to Text. Then let 'er fly! (eg: run the query)....


create table #tmp (
SQLText varchar(8000) )

create table #tmp2 (
Id int identity,
SQLText varchar(8000) )

set nocount on

delete #tmp
delete #tmp2

declare @vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)

declare csrTables cursor for
select name
from sysobjects
where type in ('u')
and name in ('Customers')
order by name


open csrTables
fetch next from csrTables into @vsTableName

while (@@fetch_status = 0)
begin

select @vsSQL = '',
@vsCols = ''
select @vsSQL = @vsSQL +
CASE when sc.type in (39,47,61,111) then
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
when sc.type = 35 then
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
else
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
end
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsCols = @vsCols + sc.name + ','
from syscolumns sc
where sc.id = object_id(@vsTableName)
order by ColID

select @vsSQL = substring(@vsSQL,1,datalength(@vsSQL)-1)

select @vsCols = substring(@vsCols,1,datalength(@vsCols)-1)

insert #tmp
exec ('select ' + @vsSQL + ' from ' + @vsTableName)

update #tmp
set sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + substring(sqltext,1,datalength(sqltext)-1) + ')'

insert #tmp2
select 'DELETE from ' + @vsTableName

insert #tmp2 values ('GO')

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' on'
end

insert #tmp2
select * from #tmp

if (select count(id) from syscolumns where id = object_id(@vsTableName) and ((status & 128) = 128) ) = 1
begin
insert #tmp2
select 'set identity_insert ' + @vsTableName + ' off'
end

insert #tmp2 values ('GO')

insert #tmp2
select 'update statistics ' + @vsTableName

insert #tmp2 values ('GO')

delete #tmp

fetch next from csrTables into @vsTableName

end

close csrTables
deallocate csrTables

update #tmp2
set sqltext = substring(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
where not(charindex(',)',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, ',''''',',null')
where not (charindex(',''''',sqltext) = 0)

update #tmp2
set sqltext = replace(sqltext, '(''''',',null')
where not (charindex('(''''',sqltext) = 0)

set nocount off

select sqltext from #tmp2 order by id

go

drop table #tmp
drop table #tmp2


The resulting output will be an auto-generated INSERT script that can be used to migrate data and/or seed a new database/tables.

Things to grok:

• HONEST DISCLAIMER: Notice that this has limitations (8000 characters of SQL per INSERT line, NO image data types). It handles about 90-95% of the needs that I use it for, but please understand that I have tweaked and embellished it over time. So use it at your own risk. If you find a bug/enhancement, please let me know.
• Notice the nifty "dynamic aggregator" queries used to build up the main @vsSQL and @vsCols variables. This pattern can be leveraged over and over to generate powerful SQL
• Don't freak about the batch of single quotes. If you are going to do dynamic SQL, you need to know how to use these. Each quote must be represented by two single quotes. For example: SELECT '''' will result in the display of one (1) single quote in the result set. The best thing to do is to experiment with this until you grok it.
• Notice the use of the "EXEC (@sql)" construct to dynamically execute SQL to generate more SQL.
• Notice the concluding few UPDATE queries to "massage" the INSERT SQL to handle NULLs and final columns in the table. Why punish yourself by embedding all of the conditional logic in the main queries when a few simple UPDATE statements can be used to "massage" the data at the end. I use this pattern routinely. This is one distinct advantage of SQL/Set based operations vs. iterative constructs...So use it!

Coming next on my blog:
Hope you found this blog entry useful. I know that this tool has been indispensable for me. So, you may be wondering..."Is this guy just a SQL junkie?" Well, the answer is yes and no. I am a SQL Junky, but not "just" a SQL junky. I also specialize in the design, architecture and implementation of enterprise-wide n-tier applications.

So, I'm going to share a bit of a blend in my next blog. See how I leverage the pervious pattern to generate VB.NET Business Classes to be consumed in an n-tier .NET Smart Client application..."Dynamic SQL begets .NET Business Classes". My theory is, I like classes and I like SQL, so why code redundant patterns by hand? The answer is, you don't have to!

Now, go and "REALLY" impress your SQL jockey friends!