注：未找到该存储过程的原始作者，我在原存储过程的基础上增加了where条件参数。可以方便的从开发环境向测试环境或生产环境部署一些配置表了。
CREATE proc spGenInsertSQL (@tablename varchar(256), @where_clause varchar(800))
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 + ‘ ‘ + @where_clause
print @sql
exec (@sql)
end
GO