自动生成INSERT语句的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 

　执行如下语句测试结果：

exec spGenInsertSQL test

 

把得到的insert语句Copy后放置在如下位置执行：　

set Identity_Insert text on

//insert 语句

set Identity_Insert test off

 

PS：有时生成SQL语句很长，采用文本方式显示时无法显示全部，通过如下方式调整:

在 SQL Server 查询窗口中单击右键－〉“查询选项”－〉在“查询选项”对话框中的“结果”下，单击“文本”－〉更改"每列中显示的最大数"