2005-08-02 T-SQL Store Procedure 修订为:
完全不受字段数量或字段值影响 而生成正确完整的 INSERT INTO ... SQL
ALTER procedure Z_SP_GenInsertSQL
(
@tablename varchar(256)
,@WhereClause varchar(1000) = 'where 1 = 1'
)
as
begin
/*
usage:
Z_SP_GenInsertSQL '表名','where '
*/
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ''(''' + char(13) + ','
set @sqlValues = ' values ('''+ char(13) + ','
select @sqlValues = @sqlValues + cols + ' + '',' + '''' + char(13) + ','
,@sql = @sql + '''[' + name + '],''' + char(13) + ','
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,175)
then 'case when '+ name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
when xtype in (231,239)
then 'case when '+ name + ' is null then ''NULL'' else ' + '''N'''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
else '''NULL'''
end as Cols
,name
from syscolumns
where id = object_id(@tablename)
--and autoval is null --忽略自增整型字段
) T
set @sql = 'select ' + char(13) + '''INSERT INTO '''+ char(13) + ','
+ '''['+ @tablename + ']''' + char(13) + ','
+ left(@sql,len(@sql)-4) + '''' + char(13) + ','')' + left(@sqlValues,len(@sqlValues)-7) + ','')'''
+ char(13) + 'from [' + @tablename + ']'
+ char(13) + @WhereClause
--select @sql -- select SQL 被截断
print @sql -- print SQL 是完整正确的
exec (@sql)
/*
select *
from syscolumns
where id = object_id('test') and autoval is null
*/
end
不过在我的数据库中有几个表未能生成成功,经分析原来数据库某些列有西文组合字符,被截断了,遂将两变量声明该为:
declare @sql nvarchar(MAX)
declare @sqlValues nvarchar(MAX)
问题解决。