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

记录下，DBeaver可视化工具上也有这个功能
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)

// 输入代码内容

EXEC sp_Data2InsertSQL YourTable
转载
https://cloud.tencent.com/developer/article/2013151