Blog Archives

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