自动生成INSERT语句的SQL存储过程

 CREATE PROCEDURE #usp_GenInsertSql (@tablename VARCHAR(256))
AS
 BEGIN
 DECLARE @sql VARCHAR(MAX)
 DECLARE @sqlValues VARCHAR(MAX)

 SET @sql =' ('
 SET @sqlValues = 'values (''+'

 SELECT @sqlValues = @sqlValues + cols + ' + '','' + ',
 @sql = @sql + QUOTENAME(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 = 167 THEN 'case when ' + name
 +
 ' is null then ''NULL'' else '
 + ''''''''' + ' + 'replace(' +
 name
 + ','''''''','''''''''''')' +
 '+'''''''''
 + ' end'
 WHEN xtype = 231 THEN 'case when ' + name
 +
 ' is null then ''NULL'' else '
 + '''N'''''' + ' + 'replace('
 +
 name
 + ','''''''','''''''''''')' +
 '+'''''''''
 + ' end'
 WHEN xtype = 175 THEN
 'case when ' + name
 + ' is null then ''NULL'' else '
 + ''''''''' + ' + 'cast(replace(' +
 name
 + ','''''''','''''''''''') as Char('
 + Cast(length AS VARCHAR) +
 '))+'''''''''
 + ' end'
 WHEN xtype = 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

go

EXEC #usp_GenInsertSql
 test