SQL 可以将表中数据转换成insert脚本的存储过程-spGenInsertSQLHan

<span style="font-family:Arial, Helvetica, sans-serif;"> 可以将表中数据转换成insert脚本的存储过程-spGenInsertSQLHan。</span>

<span style="font-family:Arial, Helvetica, sans-serif;"></span><pre name="code" class="sql">if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGenInsertSQLHan]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)   
drop procedure [dbo].[spGenInsertSQLHan]   
GO   
SET QUOTED_IDENTIFIER ON    
GO   
SET ANSI_NULLS ON    
GO   
  --exec spGenInsertSQLHan 'Fit_tb_DictType'
CREATE   proc spGenInsertSQLHan (@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   
SET QUOTED_IDENTIFIER OFF    
GO   
SET ANSI_NULLS ON    
GO