你肯定有过这样的烦恼,同样的表,不同的数据库,加入你不能执行select  insert
那么你肯定需要一条这样的存储过程,之需要传入表明,就会给你生成数据的插入语句。
当然数据表数量太大,你将最好用别的方式

复制代码 代码如下:
Create   proc [dbo].[spGenInsertSQL] (@tablename varchar(256))
as
begin
declare @ 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 ””””’+convert(char(23),’+name+’,121)+””””’ –datetime   
                     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(@,len(@sql)-1)+’) ‘ + left(@sqlValues,len(@sqlValues)-4) + ‘)” from ‘+@tablename
print @
exec (@sql)
end

SQL语句

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注