一次性将 Microsoft SQL Server 数据库中的所有表导出为平面文件格式（csv, txt）！！
3.94/5 (6投票s)
将所有 SQL 表导出为平面文件格式的最佳行业实践之一的过程演练
引言
在云计算时代，我们经常需要将数据以平面文件格式（CSV 或 txt）导出到共享驱动器（AWS S3 存储桶或 Azure BLOB 存储），然后将在云端使用这些文件进行 ETL/ELT 进一步处理。
让我们来学习一种简单的方法，将数据库中的所有表导出为平面文件！
背景
Microsoft SQL Server 的数据导出向导一次只能导出一个表到平面文件，因此手动逐个使用数据导出向导导出所有表将是一项非常耗时且繁琐的任务！
让我们来解答几个问题，以便自动将所有表导出为平面文件格式
- 当数据量很大时，最佳的数据导出方式是什么？
- 我们如何自动完成这项任务？
- 我们如何一次性将所有表导出到平面文件？？
Using the Code
让我们逐一回答上述问题！！
我们将使用 BCP（Bulk Copy Program）实用程序。我希望您熟悉这个实用程序，如果不熟悉，让我给您一个简要的介绍！！
BCP 实用程序有助于在 Microsoft SQL Server 实例和用户指定格式的数据文件之间执行批量复制操作。它可以用于将大量新行导入 SQL Server 表，或将数据从表导出到数据文件。
本文档的目的不是讨论 BCP。要阅读更多内容并下载 BCP 实用程序，请访问 Microsoft 网站。
我们将使用 T-SQL 游标 来准备脚本，并为选定数据库的每个表运行 BCP 命令，然后使用 Windows 命令 shell xp_cmdshell 从 SQL Server Management Studio (SSMS) 执行命令，以在我们的目标文件夹中创建平面文件，这些平面文件将被期望在此创建。
先决条件
- Microsoft SQL Server (2012 及更高版本)
- BCP 实用程序最新版本
- SQL Server 管理员权限（最好有）
我在我的环境中使用了 SQL Server 2017 和 AdventureWorksDW 进行测试。
让我们开始理解我们代码的各个部分！！
步骤 1：启用 MS. SQL Server 中的 xp_cmdshell
要在我们的 SQL Server 中启用 xp_cmdshell，您的 SQL 用户应具有执行这些命令的足够权限！！您可以在 Microsoft 网站上阅读有关配置的更多信息。
在连接到 Microsoft SQL Server 实例并使用具有足够权限运行此代码的用户后，可以在 SSMS 中运行以下代码。
代码片段
-- To allow advanced options to be changed.
EXECUTE sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXECUTE sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
步骤 2：检查系统上是否存在 BCP 实用程序
打开命令提示符（Windows --> 运行 --> 输入 CMD --> 单击确定）并运行以下命令。
代码片段
C:\Users\xyz> bcp /?
如果您可以看到 bcp 实用程序的帮助，则表示它已安装并可以使用；否则，您需要从 Microsoft 网站 下载并安装 BCP 实用程序。
您也可以通过以下位置检查 BCP 应用程序是否存在。
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe
步骤 3：BCP 命令
让我们快速了解 BCP 命令的准备及其在我们的脚本中的用法。
Select 查询使用 union all 将字符数据的列标题与来自表的查询结果集关联起来。通过类型转换来组合列标题的不同数据类型与表行。这将帮助我们将表数据与表标题一起写入平面文件。
代码片段
Exec master..xp_cmdshell 'bcp "SELECT ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U cybage\mohmmedmubins -t^| -T -c -d AdventureWorksDW'
语法
bcp "Select Query" queryout "OutPutFilePath"
- S InstanceName
-e "ErrorFilePath"
-U domain\UserName
-t"Seperator" e.g. -t"," or -t"|" etc.
-T is for trusted connection,when used it requires to specify network user id and password
-c uses char as the storage type,with \t (tab)
as the field separator and \r\n (newline) as the row terminator
-d DatabaseName Specifies the database to connect
步骤 4：动态查询准备
为了更好地解释，我们将大型脚本分解为更小的代码片段。让我们来理解代码中使用的各种 SQL 查询及其贡献。
代码片段
当我们选择一个数据库（例如，在我们的例子中是 AdventureWorksDW）并在 SSMS 中运行以下查询时，它将帮助我们获取所有表的名称和相关的架构 ID。
SELECT DISTINCT st.NAME,st.schema_id FROM sys.tables st where is_ms_shipped !=1;
使用选定的表准备列标题行。
代码片段：标题行
以下查询将帮助我们连接所有列的名称，这些名称将在导出数据到平面文件时作为第一行写入。
DECLARE @columnHeader VARCHAR(max)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')
+ '''' +'[' +column_name +']' +''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DimAccount'
Print @columnHeader
使用选定表的列列表准备结果集。
代码片段：结果集
以下查询将帮助我们组合所有表列的名称，并将它们转换为 NVARCHAR，以便我们可以轻松地将它们与前面代码片段中准备的表标题行合并。
DECLARE @ColumnList VARCHAR(max)
SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+
'CAST(['+column_name +'] AS NVARCHAR(max)) as [' + column_name +']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DimAccount'
Print @ColumnList
合并表标题行与结果集并准备最终查询。
代码片段：最终查询
以下脚本将帮助我们合并表标题行与表结果集。通过对结果集进行外部类型转换到 NVARCHAR，我们可以轻松地将表标题行与表结果集合并，并避免在 union all 操作期间出现任何数据类型不匹配的问题。
DECLARE @ColumnList VARCHAR(max) =''
DECLARE @columnHeaderCmdShell VARCHAR(max) =''
DECLARE @tempRaw_sql_xpcmdshell NVARCHAR(max) =''
SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+
'CAST(['+column_name +'] _
AS NVARCHAR(max)) as [' + column_name +']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DimAccount'
SELECT @columnHeaderCmdShell= COALESCE(@columnHeaderCmdShell+',' ,'')
+ '''' +'''[' +column_name +']''' +''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DimAccount'
SELECT @tempRaw_sql_xpcmdshell= 'SELECT '+
SUBSTRING(@columnHeaderCmdShell, _
2, len(@columnHeaderCmdShell))
+' UNION ALL SELECT '+_
SUBSTRING(@ColumnList, 2, len(@ColumnList))
+' FROM ' + '[dbo].[DimAccount]'
--Print @columnHeaderCmdShell
Print @tempRaw_sql_xpcmdshell
使用最终查询和各种参数准备 BCP 命令。
代码片段：准备和运行 BCP 命令
我们只需使用上面准备的最终select查询以及 BCP 命令所需的其他几个参数，并使用正确的连接语法进行连接。
最后，我们可以使用 Windows shell 实用程序 xp_cmdshell 来运行我们的命令。
DECLARE @query_xpcmdshell varchar(8000)
DECLARE @FilePath VARCHAR(1000)='D:\Data\'
DECLARE @ComputerName VARCHAR(1000) ='mohmmedmubins-w10' --instance name
DECLARE @UserName VARCHAR(1000)='domainname\mohmmedmubins' --user name
SET @query_xpcmdshell= 'bcp "' + @tempRaw_sql_xpcmdshell+'" queryout "'
+ @FilePath + @Filename+'.csv"'+' -S ' +@ComputerName
+' -e "' + @FilePath +'error.log"'
+' -U '+ @UserName
+' -t, -T -c -d ' +DB_NAME() +''
Exec master..xp_cmdshell @query_xpcmdshell
步骤 4：最终脚本
在修改了一些值，如用户名、实例名、分隔符或您想导出的文件类型后，您可以下载并运行该脚本。
代码片段
对于逗号分隔的 CSV 文件 -t^,
Exec master..xp_cmdshell 'bcp "SELECT ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U "cybage\mohmmedmubins" -t^, -T -c -d AdventureWorksDW'
对于管道 | 分隔的 CSV 文件 -t^|
Exec master..xp_cmdshell 'bcp "SELECT ''DBVersion'',''VersionDate'' _
UNION ALL SELECT CAST(DBVersion AS VARCHAR)DBVersion,_
CAST(VersionDate AS VARCHAR)VersionDate FROM AdventureWorksDWBuildVersion" _
queryout "D:\Data\AdventureWorksDWBuildVersion.csv" -S mohmmedmubins-w10 _
-U "cybage\mohmmedmubins" -t^| -T -c -d AdventureWorksDW'
对于逗号分隔的 TXT 文件，需要更改文件扩展名为 .txt。
EXEC xp_cmdshell 'bcp "SELECT CountryRegionCode, _
Name FROM [AdventureWorks].[Person].[CountryRegion]" _
queryout "D:\data\CountryRegion.txt" -T -c -t, -S mohmmedmubins-w10\mssql2017'
本文档和附带的最终脚本将为您提供一个快速的开始，请随意根据您的数据库和需求使用、增强和扩展代码。
希望您喜欢！别忘了为我们为简化您的生活所付出的努力投票。
历史
- 2020 年 7 月 15 日：初始版本