MS SQLServer 将Table中的数据导出为insert语句

在MS SQLServer中，导出数据只能使用微软-MS指定的几种格式，却不能导出为标准的Insert语句，这一点挺不好的，因为经常会有这样的需求。

如果要自动屏蔽自动增长的列，需要在查询系统表syscolumns时加上 autoval is null 的条件，见下面的红色部分。

原贴URL：http://www.im286.com/viewthread.php?tid=1195089

[SQLServer]将Table中的数据导出为insert语句

无论是上线还是平时测试，经常有这样的需求，现给出这个存储过程。

CREATE       proc dbo.proc_insert (@tablename varchar(256),@colid int=1)

/*

输入：（必选）Table名称, varchar

             （可选）从第几个栏位开始生成（比如id自增型的栏位就不需要显式insert），int

输出：产生的insert语句，每行一条。

说明：请勿在查询分析器里使用，否则只会得到被截断的语句。(用winsql可避免)

*/
as
begin
set nocount on
declare @sqlstr varchar(8000)
declare @sqlstr1 varchar(8000)
declare @sqlstr2 varchar(8000)
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+']' as name
from syscolumns a where a.colid>=@colid and a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and  a.xtype <>36 and a.autoval is null
)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



执行范例：

exec proc_insert 'picsmdl'