Deprecated: __autoload() is deprecated, use spl_autoload_register() instead in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_class_loader.funcs.php on line 55

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_misc.funcs.php on line 5137

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_misc.funcs.php on line 8521

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1442

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1447

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1453

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1460

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\files\model\_file.funcs.php on line 1465

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 49

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 50

Deprecated: define(): Declaration of case-insensitive constants is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\model\db\_db.class.php on line 51

Deprecated: Function get_magic_quotes_gpc() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_param.funcs.php on line 2112

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 817

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 818

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\plugins\model\_plugins_admin.class.php on line 1466
Dynamic SQL INSERT Generator Unleashed!
 


Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 817

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\inc\_core\_url.funcs.php on line 818
Dynamic SQL INSERT Generator Unleashed!


Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 502

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 500

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_auto_p.plugin.php on line 500

Deprecated: Array and string offset access syntax with curly braces is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\_texturize.plugin.php on line 116

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!

6 comments

Comment from:
Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\basic_antispam_plugin\_basic_antispam.plugin.php on line 330

Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\basic_antispam_plugin\_basic_antispam.plugin.php on line 330
David Walker
David Walker

Great Job again!

I only had to make a minor change on one of my tables. I had to add [ ] around the field name, because it was named Key.

Thanks again!

02/09/05 @ 10:37
Comment from: msngan
msngan

Can you list some of the sample output please?

02/09/05 @ 16:52
Comment from:
Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\basic_antispam_plugin\_basic_antispam.plugin.php on line 330
iclemartin

Jerrad Anderson (http://www.jerradanderson.com/) left a comment on http://weblogs.asp.net/wallen/archive/2005/02/09/370080.aspx

—-

I have a nice set of scripts here that also demonstrate sql creating sql.

http://www.jerradanderson.com/index.php?sectionId=17&pageId=28 Remove Comment 370435

02/10/05 @ 11:44
Comment from: Qamar hafeezi
Qamar hafeezi

Nice statement. I have been using another one but it crashes on heavy data tables. The performance of the script is not good but it generates correct statements.
One more thing, did u check sql injection in ur query?
thx
Qamar

01/19/06 @ 05:56
Comment from:
Deprecated: Function create_function() is deprecated in C:\inetpub\wwwroot\com.consultantsguild\blogs\plugins\basic_antispam_plugin\_basic_antispam.plugin.php on line 330
Mark

Thanks for the feedback Qamar. Since I run this as an administrator, I’m not particularly worried about injection. Are you referring to overruns on data fields, buffer overruns?

Mark

01/19/06 @ 07:53
Comment from: Alfredo
Alfredo

Great code!
I have only made a little modification to avoid errors with reserved words used in field names or field names with spaces. I have added this to the field names:[FieldName]

This is your code with my modifications:

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 like ‘AMEX%’
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 + quotename(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

05/23/06 @ 05:30