SQL – Generate INSERT statements

Here is a nice piece of stored procedure I found in www.sqlteam.com which can be used to generate INSERT statements for a table which has data. This is very helpful while distributing the application DB which requires some default information.

This script uses curser, so performance might be an issue in case of table with very large amount of data. There are a lot of commercial tools available to generate INSERT scripts. But those who don’t want to install some third party application for this task, this script will be an easy alternate option.


create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)

set @cols=''

declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table

open colcur

fetch next from colcur into @col

while @@fetch_status=0
begin
select @cols = @cols + ‘, ‘ + @col

fetch next from colcur into @col
end

close colcur
deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

–select @cols

declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)

select @sql = ‘select replace(”insert ‘ + @table + ‘ (‘ + @cols + ‘) ‘

select @sql = @sql + ‘values (”’

declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0
begin
if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””

select @sql=@sql + ‘ + coalesce(convert(varchar, ‘ + @colname + ‘), ”null”) + ‘

if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””
select @sql = @sql + ”’, ”’

fetch from ccur into @colname, @coltype
end

close ccur
deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ‘)”, ”””null”””, ”null”) from ‘ + @table

 

exec (@sql)

Advertisements

About Krishnaraj Barvathaya

I am Krishnaraj Barvathaya, a software developer from Bangalore and I primarily write on Microsoft Technologies and latest technical stuffs which interests me.

Posted on March 31, 2009, in .Net, SQL Server and tagged , , , , . Bookmark the permalink. Leave a comment.

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

%d bloggers like this: