sql server根据表中数据生成insert语句

-- ======================================================
-- 根据表中数据生成insert语句的存储过程
-- 建立存储过程，执行spGenInsertSQL 表名
-- 感谢playyuer
-- --感谢szyicol
-- ======================================================
CREATE proc [ dbo ] . [ spGenInsertSQL ]
( @tablename varchar ( 256 ))
as
begin
declare @sql varchar ( 8000 )
declare @sqlValues varchar ( 8000 )

set @sql = ' ( '
set @sqlValues = ' values ( '' + '
select @sqlValues = @sqlValues + cols + ' + '' , '' + ' , @sql = @sql + ' [ ' + name + ' ], '
from
( select case
when xtype in ( 48 , 52 , 56 , 59 , 60 , 62 , 104 , 106 , 108 , 122 , 127 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ' cast([ ' + name + ' ] as varchar) ' + ' end '
when xtype in ( 58 , 61 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' cast([ ' + name + ' ] as varchar) ' + ' + ''''''''' + ' end '
when xtype in ( 167 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' replace([ ' + name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
when xtype in ( 231 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ''' N '''''' + ' + ' replace([ ' + name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
when xtype in ( 175 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' cast(replace([ ' + name + ' ], '''''''' , '''''''''''' ) as Char( ' + cast (length as varchar ) + ' ))+ ''''''''' + ' end '
when xtype in ( 239 )
then ' case when [ ' + name + ' ] is null then '' NULL '' else ' + ''' N '''''' + ' + ' cast(replace([ ' + name + ' ], '''''''' , '''''''''''' ) as Char( ' + cast (length as varchar ) + ' ))+ ''''''''' + ' end '
else ''' NULL '''
end as Cols,name
from syscolumns
where id = object_id ( @tablename )
) T
set @sql = ' select '' INSERT INTO [ ' + @tablename + ' ] ' + left ( @sql , len ( @sql ) - 1 ) + ' ) ' + left ( @sqlValues , len ( @sqlValues ) - 4 ) + ' ) '' from ' + @tablename
-- print @sql
exec ( @sql )
end

-- ======================================================
-- 根据表中数据生成insert语句的存储过程
-- 建立存储过程，执行proc_insert 表名
-- 感谢Sky_blue
-- 感谢szyicol
-- ======================================================
CREATE proc [ dbo ] . [ proc_insert ] ( @tablename varchar ( 256 ))
as
begin
set nocount on
-- declare @tablename varchar(256)
-- set @tablename = 'AD'
declare @sqlstr varchar ( 4000 )
declare @sqlstr1 varchar ( 4000 )
declare @sqlstr2 varchar ( 4000 )
select @sqlstr = ' select '' insert ' + @tablename

select @sqlstr1 = ''
select @sqlstr2 = ' ( '
select @sqlstr1 = ' values ( '' + '
select @sqlstr1 = @sqlstr1 + col + ' + '' , '' + ' , @sqlstr2 = @sqlstr2 + ' [ ' + name + ' ] ' + ' , ' from ( select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype = 104 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(1),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 175 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' replace([ ' + a.name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
when a.xtype = 61 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' convert(varchar(23),[ ' + a.name + ' ],121) ' + ' + ''''''''' + ' end '
when a.xtype = 106 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar( ' + convert ( varchar ( 4 ),a.xprec + 2 ) + ' ),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 62 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(23),[ ' + a.name + ' ],2) ' + ' end '
when a.xtype = 56 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(11),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 60 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(22),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 239 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' replace([ ' + a.name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
when a.xtype = 108 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar( ' + convert ( varchar ( 4 ),a.xprec + 2 ) + ' ),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 231 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' replace([ ' + a.name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
when a.xtype = 59 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(23),[ ' + a.name + ' ],2) ' + ' end '
when a.xtype = 58 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' convert(varchar(23),[ ' + a.name + ' ],121) ' + ' + ''''''''' + ' end '
when a.xtype = 52 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(12),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 122 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(22),[ ' + a.name + ' ]) ' + ' end '
when a.xtype = 48 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ' convert(varchar(6),[ ' + a.name + ' ]) ' + ' end '
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype = 167 then ' case when [ ' + a.name + ' ] is null then '' NULL '' else ' + ''''''''' + ' + ' replace([ ' + a.name + ' ], '''''''' , '''''''''''' ) ' + ' + ''''''''' + ' end '
else ''' NULL '''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id ( @tablename ) and a.xtype <> 189 and a.xtype <> 34 and a.xtype <> 35 and a.xtype <> 36
)t order by colid
select @sqlstr = @sqlstr +left ( @sqlstr2 , len ( @sqlstr2 ) - 1 ) + ' ) ' +left ( @sqlstr1 , len ( @sqlstr1 ) - 3 ) + ' ) '' from ' + @tablename
-- print @sqlstr
exec ( @sqlstr )
set nocount off
end