将数据库记录倒为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

转载于:https://www.cnblogs.com/grokyao/archive/2011/06/15/2081689.html