MSSQL2005 데이터를 INSERT문으로 생성

SQL2008은 INSERT문으로 내려받아지고,

SQL2005는 아래 프로시져로 만든후에,   

exec [dbo].[Generate_insert_sql] '테이블명';  로 실행함.

CREATE PROCEDURE [dbo].[Generate_insert_sql]

@table varchar(50)

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','text')

select @sql=@sql + ''''''

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

if @coltype in ('varchar', 'char', 'datetime','text')

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)