MS-SQL生成表里数据的insert插入语句

来源：互联网发布：plc编程100例梯形图编辑：程序博客网时间：2024/06/11 09:09

还在为不想备份，表里的不能直接生成insert插入语句保存而郁闷吗。

以下存储过程可解决烦恼

SET ANSI_NULLS ON  GO    SET QUOTED_IDENTIFIER ON  GO    -- =============================================  -- Author:      <wilson,chan>  -- Create date: <2011-11-20,11:06,>  -- Description: <返回表数据的sql插入语句,,>  -- =============================================  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 '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

原代码出自：http://blog.csdn.net/neso520/article/details/7086502