自动生成INSERT语句的SQL存储过程

在SQL Server数据库中，如果需要让INSERT语句自动生成，应该采用何种方法呢？下面就将为您介绍自动生成INSERT语句的SQL存储过程，供您参考。

这是建立一个存储过程，通过执行这个存储过程，将指定的数据表中的数据导成Insert语句形式

–这是一个可以将表中的数据自动生成INSERT语句的存储过程

–有个BUG，如果是自动增长列，数据也会被插入

–在查询分析器中执行完成后，可以从执行结果中自制出SQL脚本。

IF OBJECT_ID(‘spGenInsertSQL’,’P’) IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE   proc 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
GO

执行如下语句测试结果：

exec spGenInsertSQL test

【编辑推荐】

SQL语句中的SELECT DISTINCT

SQL数据库压缩语句简介

教您用事务一次处理多条SQL语句

通过执行多条SQL语句实现数据库事务

存储过程优化的SQL语句写法