如何将表中的所有数据导出为可插入的SQL格式？
如何将表中的所有数据导出为可插入的SQL格式？
有一种方法可以通过DumpDataFromTable存储过程从表中将数据转储为文件：
EXEC dbo.DumpDataFromTable
@SchemaName = 'dbo',
@TableName = 'YourTableName',
@FolderPath = N'c:\tmp\scripts\' -- 文件夹必须存在!!!
注意：SQL必须具有创建文件的权限，如果没有设置，则执行下面的命令：
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
使用此脚本，您可以调用存储过程DumpDataFromTable.sql，并一次转储多个表，而不是手动逐个从管理工具中进行操作。
生成的脚本默认格式为：
INSERT INTOSELECT
或者，您可以将生成的格式更改为：
SELECT ... FROM
通过设置变量@OutputType = 2。
完整的存储过程代码如下：
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpDataFromTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.[DumpDataFromTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 作者：Oleg Ciobanu
-- 创建日期：20171214
-- 版本：1.02
-- 描述：
-- 转储数据有两种格式
-- @OutputType = 1 : INSERT INTO 格式
-- @OutputType = 2 : SELECT * FROM 格式
--
-- SQL必须具有创建文件的权限，如果没有设置，则执行下面的命令：
-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
--
-- =============================================
CREATE PROCEDURE [dbo].[DumpDataFromTable]
(
@SchemaName nvarchar(128) = 'dbo',
@TableName nvarchar(128) = 'testTable',
@AdditionalCondition nvarchar (1000) = '', -- 必须以AND开头
@OutputType int = 1, -- 取值为1表示INSERT INTO格式，取值为2表示SELECT FROM格式
@FolderPath nvarchar(250) = N'c:\tmp\scripts\', -- 文件夹必须存在!!!
@FileName nvarchar(250) = NULL, -- 如果传入此值，将使用此值作为文件名
@Debug int = 0
)
AS
BEGIN
SET NOCOUNT ON;
-- 如果收到权限拒绝的错误，请执行下面的命令
-- EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE WITH OVERRIDE;
DECLARE @Script nvarchar (max)
DECLARE @TableNameClean nvarchar (max) = ''
DECLARE @ColumnNameClean nvarchar(max)
DECLARE @ColumnList nvarchar(max)
-- 清理数据
SET @TableNameClean = REPLACE(REPLACE(@TableName,'[',''),']','')
SET @ColumnNameClean = REPLACE(REPLACE(@ColumnName,'[',''),']','')
SET @AdditionalCondition = NULLIF(@AdditionalCondition,'')
SET @FileName = REPLACE(@ColumnName,'.','_')
SET @FileName = COALESCE(@FolderPath + @TableNameClean + '.sql', @FolderPath + @TableNameClean + ISNULL('_' + @FileName,N'') + '.sql')
-- 调试
IF @Debug = 1
PRINT @FileName
-- 创建临时存储过程，用于生成脚本文件
DECLARE @_WritereadFile VARCHAR(max) =
'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''PRC_WritereadFile'')
BEGIN
DROP Procedure PRC_WritereadFile
END;'
EXEC (@_WritereadFile)
-- 存储过程内容
SET @_WritereadFile =
'CREATE Procedure PRC_WritereadFile (
@Recreate INT -- 0表示创建，1表示追加模式
, @FilePath NVARCHAR(1000)
, @FileName NVARCHAR(500)
, @Text NVARCHAR(MAX)
)
AS
DECLARE @FileSystem INT
DECLARE @FileHandle INT
DECLARE @ForAppending INT
DECLARE @ForReading INT
DECLARE @ForWriting INT
DECLARE @Result INT
DECLARE @ErrorMessage NVARCHAR(1500) = ''Error creating FileSystemObject''
-- 创建对象
EXECUTE @Result = sp_OACreate ''Scripting.FileSystemObject'', @FileSystem OUTPUT
IF @Result <> 0 BEGIN
PRINT ''Scripting.FileSystemObject''
GOTO Error_Handler
END
-- 创建文件
IF @Recreate = 0 BEGIN
EXECUTE @Result = sp_OAMethod @FileSystem,''CreateTextFile'', @FileHandle OUTPUT, @FilePath + @FileName
IF @Result <> 0 BEGIN
PRINT ''CreateTextFile''
GOTO Error_Handler
END
END ELSE BEGIN
EXECUTE @Result = sp_OAMethod @FileSystem,''OpenTextFile'', @FileHandle OUTPUT, @FilePath + @FileName, 8, 0 -- 8表示追加模式
IF @Result <> 0 BEGIN
PRINT ''OpenTextFile''
GOTO Error_Handler
END
END
-- 写入文本
EXECUTE @Result = sp_OAMethod @FileHandle, ''WriteLine'', NULL, @Text
IF @Result <> 0 BEGIN
PRINT ''WriteLine''
GOTO Error_Handler
END
-- 关闭文件
EXECUTE @Result = sp_OAMethod @FileHandle,''Close''
IF @Result <> 0 BEGIN
PRINT ''Close''
GOTO Error_Handler
END
-- 销毁对象
EXECUTE sp_OADestroy @FileHandle
EXECUTE sp_OADestroy @FileSystem
GOTO Done
Error_Handler:
DECLARE @ErrorNumber VARCHAR(30), @ErrorDescription VARCHAR (200)
EXEC @Result = sp_OAGetErrorInfo @FileHandle, @ErrorNumber OUT, @ErrorDescription OUT
PRINT ''*** ERROR ***''
SELECT OLEResult = @Result, hr = CONVERT (binary(4), @ErrorNumber), source = @FileHandle, description = @ErrorDescription
Done:
'
-- 调试
IF @Debug = 1
PRINT @_WritereadFile
EXEC (@_WritereadFile)
-- 调试
IF @Debug = 1
PRINT @Script
-- 构建查询脚本
SET @Script = '
SELECT
u.rn
, c.ORDINAL_POSITION as ColumnPosition
, c.DATA_TYPE as ColumnType
, u.ColumnName
, u.ColumnValue
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
'
+ CHAR(13) + @ColumnList
+ CHAR(13) + 'FROM [' + @SchemaName + '].[' + @TableName + '] s'
+ CHAR(13) + 'WHERE 1 = 1'
+ CHAR(13) + COALESCE(@AdditionalCondition,'')
+ CHAR(13) + ') tt
UNPIVOT
(
ColumnValue
FOR ColumnName in ('
+ CHAR(13) + @ColumnNameClean
+ CHAR(13) + ')
) u
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = u.ColumnName
AND c.TABLE_SCHEMA = ''' + @SchemaName + '''
AND c.TABLE_NAME = ''' + @TableName + '''
ORDER BY u.rn
, c.ORDINAL_POSITION
'
-- 调试
IF @Debug = 1
PRINT @Script
-- 准备数据表
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
CREATE TABLE #tmp
(
rn bigint
,ColumnPosition int
,ColumnType varchar (128)
,ColumnName varchar (128)
,ColumnValue nvarchar (2000)
)
SET @Script = 'INSERT INTO #tmp ' + CHAR(13) + @Script
-- 调试
IF @Debug = 1
PRINT @Script
EXEC (@Script)
-- 插入一个虚拟行，否则最后一个记录将无法处理 :)
INSERT INTO #tmp (rn)
SELECT MAX(rn) + 1
FROM #tmp
-- 调试
IF @Debug = 1
SELECT * FROM #tmp
DECLARE @RowNumber bigint
, @ColumnPosition int
, @ColumnType varchar (128)
, @ColumnName varchar (128)
, @ColumnValue nvarchar (2000)
, @Flag int = -1 -- 计数器/标志
, @LineCount int = 1 -- 行计数器
, @Line nvarchar(max) = NULL
, @OutputScript nvarchar(max) = NULL
DECLARE cur CURSOR FOR
SELECT rn, ColumnPosition, ColumnType, ColumnName, ColumnValue
FROM #tmp
ORDER BY rn, ColumnPosition
OPEN cur
FETCH NEXT FROM cur
INTO @RowNumber, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue
-- 调试
IF @Debug = 1
BEGIN
SET @OutputScript = 'SET NOCOUNT ON;' + CHAR(13)
EXEC PRC_WritereadFile 1 /*Add*/, @FolderPath, @FileName, @OutputScript
SET @OutputScript = ''
END
ELSE BEGIN
SET @OutputScript = 'SET NOCOUNT ON;' + CHAR(13)
SET @OutputScript =
@OutputScript + 'SELECT *'
+ CHAR(13) + 'FROM ('
+ CHAR(13) + 'VALUES'
EXEC PRC_WritereadFile 1 /*Add*/, @FolderPath, @FileName, @OutputScript
SET @OutputScript = NULL
END
WHILE @_STATUS = 0
BEGIN
IF (@RowNumber <> @Flag) -- 新行
BEGIN
IF @OutputType = 1 -- 默认为INSERT INTO格式
BEGIN
SET @OutputScript = 'INSERT INTO [' + @SchemaName + '].[' + @TableName + '] ('
+ CHAR(13) + @ColumnList
+ CHAR(13) + ')'
+ CHAR(13) + 'VALUES ('
+ @OutputScript
+ CHAR(13) + ');'
END
ELSE
BEGIN
-- 构建SELECT FROM格式
IF (@RowNumber <> @Flag) -- 新行
BEGIN
SET @OutputScript = COALESCE(@OutputScript + ',','') + '(' + @OutputScript + ')'
EXEC PRC_WritereadFile 1 /*Add*/, @FolderPath, @FileName, @OutputScript
SET @OutputScript = '' -- 在方法2中应清除脚本
END
END
-- 调试
IF @Debug = 1
PRINT @OutputScript
EXEC PRC_WritereadFile 1 /*Add*/, @FolderPath, @FileName, @OutputScript
-- 有新行
-- 初始化变量
SET @ColumnList = ''
SET @OutputScript = NULL
SET @Flag = @RowNumber
END
-- 构建插入值
IF (@RowNumber = @Flag) -- 同一行
BEGIN
SET @ColumnList = COALESCE(@ColumnList + ',','') + '[' + @ColumnName + ']'
SET @OutputScript = CASE
WHEN @ColumnType IN ('bit','tinyint','smallint','int','bigint'
,'money','real','float','decimal','numeric','smallmoney')
THEN
COALESCE(@OutputScript + ',','') + '' + ISNULL(RTRIM(@ColumnValue),'NULL') + ''
ELSE
COALESCE(@OutputScript + ',','') + '''' + ISNULL(RTRIM(REPLACE(@ColumnValue, '''', '''''')),'NULL') + ''''
END
END
FETCH NEXT FROM cur
INTO @RowNumber, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue
-- 调试
IF @Debug = 1
BEGIN
PRINT CAST(@RowNumber AS VARCHAR) + '-' + CAST(@Flag AS VARCHAR)
END
END
CLOSE cur
DEALLOCATE cur
IF @OutputType = 1
BEGIN
PRINT 'ignore'
END
ELSE BEGIN
SET @OutputScript = CHAR(13) + ') AS vtable '
+ CHAR(13) + ' (' + @ColumnList
+ CHAR(13) + ')'
EXEC PRC_WritereadFile 1 /*Add*/, @FolderPath, @FileName, @OutputScript
SET @OutputScript = NULL
END
PRINT 'Done: ' + @FileName
END
或者可以从https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts下载最新版本。
它没有返回所有记录。我对一个包含3条记录的表进行了备份，但生成的脚本只有2条。
这是一个错误，我在github上修复了这个问题，请参见链接。
问题出现的原因是有人想要从一个表中导出所有数据到一个可插入的SQL格式中。解决方法有两种：
1. 如果目标表（B_table）没有自增列（identity column），可以使用以下代码将源表（A_table）的所有记录插入到目标表中：
INSERT INTO A_db.dbo.A_table SELECT * FROM B_db.dbo.B_table
2. 如果目标表（B_table）有自增列（identity column），则需要指定要插入的列。基本上，选择除了自增列以外的所有列，自增列会默认自动递增。
SELECT * INTO B_db.dbo.B_table FROM A_db.dbo.A_table
如果在B_db中不存在B_table，上述代码将在B_db数据库中创建一个具有所有现有值的B_table表。
在SSMS 2017中，导出表中的所有数据并以可插入的SQL格式进行导出的方法如下：
1. 右键单击数据库
2. 指向“tasks”（在SSMS 2017中，您需要忽略第2步，因为生成脚本选项位于上下文菜单的顶级）。感谢Daniel的评论更新。
3. 选择“generate scripts”
4. 点击下一步
5. 选择表
6. 点击下一步
7. 点击高级
8. 滚动到“Types of data to script” - 在SMSS 2014中称为“types of data to script”。感谢Ellesedil的评论。
9. 选择“data only”
10. 点击“Ok”关闭高级脚本选项窗口
11. 点击下一步并生成脚本
通常我会将生成的脚本复制到一个新的查询编辑器窗口中，然后根据需要进行修改。
有人问是否可以生成UPDATE语句而不是插入语句。
警告：此工具以特定于文化的格式导出日期时间值。在其他服务器上可能无法导入。
有人评论说，如果导出包含NULL值的数据，则无法很好地工作。您将在结果中得到N''，并且两个单引号之间的字符将是二进制零。这将使结果无效，您将无法在SQL Server Management Studio中运行该脚本（未终止的字符串错误）。唯一的解决方法似乎是手动将所有内容更改为N''，或编写自己的替换工具以过滤掉零（因为没有文本编辑器会处理它，因为零只是字符串的结束）。
在SSMS 2017中，您需要忽略第2步 - 生成脚本选项位于上下文菜单的顶级。
是的，但是...如果只有一个表，您可以选择“script table as...” - “update”，但是对于所有表，您只能选择“DROP and CREATE”或“CREATE”在“Advanced”配置中。我正在使用SMSS 2019。
它要求连接字符串...为什么这么晦涩难懂。
它不会生成脚本中的数据。
我找不到v18.6中的生成脚本或任务选项。
在2023年的SSMS 18.12.1仍然有效。