批量导出数据库表到平面文件的简易方法

在云平台如AWS S3桶或Azure BLOB存储中,将数据导出为平面文件格式(CSV或TXT)是数据迁移和ETL/ELT处理的常见需求。本文将介绍一种简便的方法,使用SQL Server的BCP(Bulk Copy Program)工具和T-SQL脚本,实现数据库中所有表的批量导出。

Microsoft SQL Server的导出数据向导允许逐个表导出到平面文件,如果手动逐个使用数据导出向导,将非常耗时且繁琐。本文将探讨如何自动批量导出所有表到平面文件。

问题探讨

在数据量非常大的情况下,如何优化数据导出?如何实现自动化任务?如何一次性导出所有表?

使用代码

将逐一回答上述问题。首先,将使用BCP工具,这是一个用于在MicrosoftSQL Server实例和用户指定格式的数据文件之间执行批量复制操作的工具。

前提条件

需要满足以下条件:

  • Microsoft SQL Server(2012及更高版本)
  • 最新版本的BCP工具
  • SQL Server的管理员访问权限(最好有)

本文以SQL Server 2017和AdventureWorksDW数据库为例进行测试。

步骤1:启用xp_cmdshell

在SQL Server中启用xp_cmdshell,需要有足够的权限执行这些命令。以下是启用xp_cmdshell的代码示例:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

步骤2:检查BCP工具的存在

打开命令提示符,运行命令`bcp /?`,如果能看到帮助信息,则表示BCP工具已安装并可用。

步骤3:BCP命令

理解BCP命令的准备和使用。以下是使用BCP命令导出数据的代码示例:

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';

步骤4:动态查询准备

以下是准备动态查询的代码示例,包括获取所有表名和相关架构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;
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;
DECLARE @tempRaw_sql_xpcmdshell NVARCHAR(max);
SELECT @tempRaw_sql_xpcmdshell = 'SELECT ' + SUBSTRING(@columnHeaderCmdShell, 2, LEN(@columnHeaderCmdShell)) + ' UNION ALL SELECT ' + SUBSTRING(@ColumnList, 2, LEN(@ColumnList)) + ' FROM [dbo].[DimAccount]';
PRINT @tempRaw_sql_xpcmdshell;

步骤5:最终脚本

以下是最终脚本的代码示例,可以下载并运行,只需修改用户名、实例名、分隔符或希望导出的文件类型等值。

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';

本文和附带的最终脚本将为提供快速入门,可以自由使用、增强和扩展代码以满足数据库和需求。

沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485