SQL Server存储过程生成insert语句实例

前端技术 2023/09/06 MSSQL

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

复制代码 代码如下:

Create   proc [dbo].[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 \'\'\'\'\'\'\'\'\'+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(@sql,len(@sql)-1)+\') \' + left(@sqlValues,len(@sqlValues)-4) + \')\'\' from \'+@tablename
print @sql
exec (@sql)
end

SQL语句

本文地址：https://www.stayed.cn/item/15240

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案