将数据库记录倒为Insert语句的存储过程

GO
/****** Object:  StoredProcedure [dbo].[SinaBlog_Album_CreateAlbum]    Script Date: 06/15/2011 17:40:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <grokyao>
-- Create date: <2011-06-15>
-- Description:    <将表中数据倒为Insert语句>
-- =============================================
CREATE   PROCEDURE 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   
  
  
    EXEC (@sql)   
  
END 

技术改变生活！

转载地址： http://www.cnblogs.com/grokyao/archive/2011/06/15/2081689.html