SQL SERVER将查询数据转为INSERT语句

CREATE PROC sp_Data2InsertSQL
@TableName AS VARCHAR(100)
AS
 
DECLARE	xCursor CURSOR FOR
 
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
 
DECLARE @F1 VARCHAR(100)
DECLARE @F2 integer
DECLARE @SQL varchar(8000)
 
SET @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
 
OPEN xCursor
FETCH xCursor INTO @F1,@F2
WHILE @@FETCH_STATUS = 0
 
BEGIN
SET @sql = @sql + ''
      + CASE WHEN @F2 IN (35,58,99,167,175,231,239,61) then ' + CASE WHEN ' + 
        @F1 + ' IS NULL THEN '''' ELSE '''''''' END + ' ELSE '+' END 
      + 'REPLACE(ISNULL(CAST(' + @F1 + ' AS VARCHAR(8000)),''NULL''),'''''''','''''''''''')'     
      + CASE WHEN @F2 IN (35,58,99,167,175,231,239,61) THEN ' + CASE WHEN ' + 
        @F1 + ' IS NULL THEN '''' ELSE '''''''' END + ' ELSE '+' END    
      + CHAR(13) + ''','''
FETCH NEXT FROM xCursor INTO @F1,@F2
END
 
CLOSE xCursor
 
DEALLOCATE xCursor
 
SET @sql = LEFT(@sql,LEN(@sql) - 5) + ' + '')'' FROM ' + @TableName
 
EXEC (@sql)
 
GO

// 输入代码内容

EXEC sp_Data2InsertSQL YourTable