SQL Tips by Namwar Rizvi

May 22, 2007

Insert Script Generator

Filed under: Insert Script Generator,SQL Server 2005,tips,TSQL — namwar @ 9:18 PM

Every SQL developer feels the need for INSERT statements generator script for a given table data. I have found a very good script to accomplish this task. With some minor modifications I have created the following stored procedure which takes table name as input and generates insert statements. Following is the stored procedure code:

Create Proc spInsertScriptGenerator
(
@p_tableName varchar(255)
)
as
/**************************************************************/
–Description : Stored procedure to generate insert statements
–Original Source : http://blogs.consultantsguild.com/index.php/mclerget/2005/02/07/dynamic_sql_insert_generator_unleashed_1
–Modified By : Namwar Rizvi
–Date : 22-May-2007

/**************************************************************/
Declare @tmp table
(
SQLText varchar(8000)
)
Declare @tmp2 table
(
Id int identity,
SQLText varchar(8000)

)
set nocount on
declare
@vsSQL varchar(8000),
@vsCols varchar(8000),
@vsTableName varchar(40)

declare csrTables cursor for
select name from sysobjects where type in (‘u’)and name =@p_tableName
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

Advertisements

4 Comments »

  1. Excellent!

    Comment by msvmuthu — November 28, 2008 @ 3:45 AM | Reply

  2. Outstanding info. I will definitely visit again.

    Comment by irradrady — May 20, 2009 @ 8:11 PM | Reply

  3. quotes giving problems. I need sql file to download.

    Comment by Ashka — May 28, 2009 @ 9:10 AM | Reply

  4. This script doesn’t work;

    1.) all the quotes display wrongly in the HTML on this page, meaning copy & paste is a no no.
    2.) If you try and find/replace the quotes out and replace with the correct single & double quotes, it still throws errors when running in an attempt to create the stored proc against a SQL Server data source.

    Offer the (working) script as a downloadable archive (zip/rar) with a readme perhaps? It will enable people to at least use the script, which does offer quite a useful piece of functionality!

    -Bob (Pseud)

    Comment by Bob — June 24, 2009 @ 1:12 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: