在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'