如何使用 bcp 实用工具

适用于：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics分析平台系统（PDW）Microsoft Fabric中的SQL数据库

大容量复制程序实用工具 （bcp） 在 SQL Server 实例与用户指定格式的数据文件之间大容量复制数据。

注解

• 有关在何处查找或如何运行 bcp 实用工具以及命令提示符实用工具语法约定的信息，请参阅 SQL 命令行实用工具（数据库引擎）。

• 有关准备数据以进行批量导入或导出操作的信息，请参阅准备用于批量导出或导入的数据。

• 有关何时在事务日志中记录由批量导入执行的行插入操作的信息，请参阅批量导入的最小日志记录的先决条件。

• 字符<、>、|和&^都是特殊命令 shell 字符，并且它们前面必须带有转义字符（^），或在字符串中使用时用引号括起来（例如）。 "StringContaining&Symbol" 如果使用引号将包含特殊字符的字符串引起来，则这些引号将成为环境变量值的一部分。 有关详细信息，请参阅 “使用其他特殊字符”。

原生数据文件支持

在 SQL Server 中，bcp 实用工具支持与 SQL Server 2000 (8.x) 及更高版本的 SQL Server 版本兼容的本机数据文件。

计算列和时间戳列

数据文件中针对计算列或 timestamp 列导入的值将被忽略， SQL Server 将自动分配该列的值。 如果数据文件不包含表中的计算列或时间戳列的值，则可使用格式化文件指定应在导入数据时忽略表中的计算列或时间戳列；SQL Server 将自动为该列分配值。

计算列和 timestamp 列会照常从 SQL Server 大容量复制到数据文件中。

指定包含空格或引号的标识符

SQL Server 标识符可以包含嵌入的空格和引号等字符。 此类标识符必须按以下方式处理：

• 如果在命令指示符处指定的标识符或文件名包含空格或引号，则需用英文双引号 ("") 将该标识符引起来。

  例如，下面的 bcp out 命令创建了一个名为 Currency Types.dat的数据文件：

  bcp AdventureWorks2022.Sales.Currency out "Currency Types.dat" -T -c
  

• 若要指定包含空格或引号的数据库名称，必须使用 -q 选项。

• 对于包含嵌入空格或引号的所有者、表或视图的名称，可以选择以下其中之一：

  • 指定 -q 选项，或者

  • 将所有者、表或视图的名称括在引号内的括号 ([]) 中。

数据验证

bcp 现在会强制执行数据验证和数据检查，这样，在对数据文件中的无效数据执行脚本时，可能会导致脚本失败。 例如， bcp 现在可以验证：

• float 或 real 数据类型的原生表示形式是有效的。

• Unicode 数据的字节数为偶数。

可以在早期版本的 SQL Server 中批量导入的无效数据类型现在可能无法加载。在早期版本中，仅当客户端尝试访问无效数据时才出现失败。 在大容量加载后查询数据时，添加的验证可最大限度地减少意外结果。

批量导出或导入 SQLXML 文档

若要批量导出或导入 SQLXML 数据，请在格式化文件中使用下列数据类型之一。

字符模式 (-c) 和原生模式 (-n) 最佳做法

本节提供关于字符模式 (-c) 和原生模式 (-n) 的一些推荐建议。

• （管理员/用户）应尽可能使用本机格式 (-n) 以避免分隔符问题。 使用 SQL Server 的原生格式进行导入和导出。 如果计划将数据导出到非 SQL Server 数据库，请使用 -c 或 -w 选项从 SQL Server 导出数据。

• （管理员）使用 bcp out 时验证数据。 例如，使用 bcp out、bcp in，然后又使用 bcp out 时，请验证数据是否正确导出，并确保终止符值不会作为某些数据值的一部分。 请考虑使用随机的十六进制值覆盖默认的终止符（使用 -t 和 -r 选项），以便避免终止符值和数据值之间的冲突。

• （用户）使用长且唯一的终止符（任意字节或字符序列）可以最大程度减少与实际字符串值冲突的可能性。 这可以通过使用 -t 和 -r 选项实现。

示例

本节中的示例使用 SQL Server 2016 (13.x) 及更高版本的 WideWorldImporters 示例数据库、Azure SQL 数据库和 Azure SQL 托管实例。 可以从 WideWorldImporters 下载 https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0。 有关用于还原示例数据库的语法，请参阅 RESTORE 语句。

示例测试条件

除了另行指定的位置，该示例假定你使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有可信连接。 在许多示例中都使用一个名为 D:\bcp 的目录。 将 <server_name> 和其他占位符值替换为您环境中的值。

以下 Transact-SQL 脚本创建 WideWorldImporters.Warehouse.StockItemTransactions 表的空副本，然后添加主键约束：

USE WideWorldImporters;
GO

SET NOCOUNT ON;

IF NOT EXISTS (SELECT *
               FROM sys.tables
               WHERE name = 'Warehouse.StockItemTransactions_bcp')
    BEGIN
        SELECT *
        INTO WideWorldImporters.Warehouse.StockItemTransactions_bcp
        FROM WideWorldImporters.Warehouse.StockItemTransactions
        WHERE 1 = 2;

        ALTER TABLE Warehouse.StockItemTransactions_bcp
        ADD CONSTRAINT PK_Warehouse_StockItemTransactions_bcp
            PRIMARY KEY NONCLUSTERED (StockItemTransactionID ASC);
    END

可以根据需要截断 StockItemTransactions_bcp 表：

TRUNCATE TABLE WideWorldImporters.Warehouse.StockItemTransactions_bcp;

A. 识别 bcp 实用工具版本

在命令提示符处输入以下命令：

bcp -v

B. 将表行复制到数据文件中（使用信任连接）

以下示例阐释了 out 表中的 WideWorldImporters.Warehouse.StockItemTransactions 选项。

• 基本

  此示例创建一个名为 StockItemTransactions_character.bcp 的数据文件，并使用 字符 格式将表数据复制到该文件中。

  在命令提示符处输入以下命令：

  bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  此示例创建一个名为 StockItemTransactions_native.bcp 的数据文件，并使用 原生格式 将表数据复制到该文件中。 此示例还指定最大语法错误数、一个错误文件和一个输出文件。

  在命令提示符处输入以下命令：

  bcp WideWorldImporters.Warehouse.StockItemTransactions OUT D:\bcp\StockItemTransactions_native.bcp -m 1 -n -e D:\bcp\Error_out.log -o D:\bcp\Output_out.log -S <server_name> -T
  

查看 Error_out.log 和 Output_out.log。 Error_out.log 应为空白。 比较 StockItemTransactions_character.bcp 与 StockItemTransactions_native.bcp之间的文件大小。

C. 将表行复制到数据文件中（使用混合模式身份验证）

下面的示例阐释了 out 表中的 WideWorldImporters.Warehouse.StockItemTransactions 选项。 此示例创建一个名为 StockItemTransactions_character.bcp 的数据文件，并使用 字符 格式将表数据复制到该文件中。

该示例假定你使用混合模式身份验证，必须使用 -U 开关指定登录 ID。 并且，除非你连接到本地计算机上 SQL Server 的默认实例，否则请使用 -S 开关指定系统名称和实例名称（可选）。

在命令提示符处，输入以下命令：（系统将提示你输入密码。）

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\bcp\StockItemTransactions_character.bcp -c -U<login_id> -S<server_name\instance_name>

D. 将文件中的数据复制到表中

以下示例使用前面创建的文件阐明 in 表中的 WideWorldImporters.Warehouse.StockItemTransactions_bcp 选项。

• 基本

  此示例使用以前创建的 StockItemTransactions_character.bcp 数据文件。

  在命令提示符处输入以下命令：

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_character.bcp -c -T
  

• Expanded

  此示例使用以前创建的 StockItemTransactions_native.bcp 数据文件。 该示例还使用 TABLOCK 提示，并指定批大小、最大语法错误数、错误文件和输出文件。

  在命令提示符处输入以下命令：

  bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\bcp\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\bcp\Error_in.log -o D:\bcp\Output_in.log -S <server_name> -T
  

  查看 Error_in.log 和 Output_in.log。

E. 将特定的列复制到数据文件中

若要复制特定列，可以使用 queryout 选项。 下面的示例仅将 StockItemTransactionID 表中的 Warehouse.StockItemTransactions 列复制到数据文件中。

在命令提示符处输入以下命令：

bcp "SELECT StockItemTransactionID FROM WideWorldImporters.Warehouse.StockItemTransactions WITH (NOLOCK)" queryout D:\bcp\StockItemTransactionID_c.bcp -c -T

F. 将特定的行复制到数据文件中

若要复制特定行，可以使用 queryout 选项。 以下示例仅将名为 Amy Trefl 的人员行从 WideWorldImporters.Application.People 表复制到数据文件 Amy_Trefl_c.bcp中。

在命令提示符处输入以下命令：

bcp "SELECT * from Application.People WHERE FullName = 'Amy Trefl'" queryout D:\bcp\Amy_Trefl_c.bcp -d WideWorldImporters -c -T

G. 将查询中的数据复制到数据文件中

若要将 Transact-SQL 语句的结果集复制到数据文件中，请使用 queryout 选项。 下面的示例将 WideWorldImporters.Application.People 表中的姓名复制到 People.txt 数据文件中；这些姓名按全名排序。

在命令提示符处输入以下命令：

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\bcp\People.txt -t, -c -T

H. 创建格式化文件

下面的示例为 Warehouse.StockItemTransactions 数据库中的 WideWorldImporters 表创建三个不同格式文件。 查看创建的每个文件的内容。

在命令提示符处输入以下命令：

REM non-XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.fmt -c -T

REM non-XML native format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_n.fmt -n -T

REM XML character format
bcp WideWorldImporters.Warehouse.StockItemTransactions format nul -f D:\bcp\StockItemTransactions_c.xml -x -c -T

有关详细信息，请参阅使用非 XML 格式化文件（SQL Server）和 XML 格式化文件（SQL Server）。

I. 使用格式化文件进行 bcp 批量导入

向 SQL Server的实例中导入数据时，若要使用以前创建的格式化文件，请同时使用 -f 开关和 in 选项。 例如，以下命令通过使用以前创建的格式化文件 ( StockItemTransactions_character.bcp)，将数据文件 Warehouse.StockItemTransactions_bcp 的内容大容量复制到 StockItemTransactions_c.xml表的副本中。

在命令提示符处输入以下命令：

bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp in D:\bcp\StockItemTransactions_character.bcp -L 100 -f D:\bcp\StockItemTransactions_c.xml -T

J. 指定一个代码页

以下部分代码示例显示了指定代码页 65001 时使用的 bcp import 语句：

bcp MyTable in "D:\data.csv" -T -c -C 65001 -t , ...

K. 使用自定义字段和行终止符的示例输出文件

此示例演示了两个示例文件，这些文件由 bcp 使用自定义字段和行终止符生成。

1. 在 dbo.T1 数据库中创建一个表 tempdb，其中包含两列，即 ID 和 Name。

   USE tempdb;
   GO
   
   CREATE TABLE dbo.T1
   (
       ID INT,
       [Name] NVARCHAR (20)
   );
   GO
   
   INSERT INTO dbo.T1 VALUES (1, N'Natalia');
   INSERT INTO dbo.T1 VALUES (2, N'Mark');
   INSERT INTO dbo.T1 VALUES (3, N'Randolph');
   GO
   

2. 使用自定义字段终止符从示例表 dbo.T1 生成输出文件。

   在此示例中， -t , 指定自定义字段终止符。 将 <server_name> 替换为您环境中的一个特定值。

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t ,
   

   结果集如下。

   1,Natalia
   2,Mark
   3,Randolph
   

3. 使用自定义字段终止符和自定义行终止符从示例表 dbo.T1 生成输出文件。

   在此示例中， -t , 指定自定义字段终止符，并 -r : 指定自定义行终止符。 将 <server_name> 替换为你的环境的值。

   bcp dbo.T1 out T1.txt -T -S <server_name> -d tempdb -w -t , -r :
   

   结果集如下。

   1,Natalia:2,Mark:3,Randolph:
   

   注释

   行终止符将始终添加，即便是最后一条记录。 但是，字段终止符不会添加到最后一个字段。

其他示例

以下文章包含有关使用 bcp 的示例：

• 批量导入或批量导出的数据格式（SQL Server）

  • 使用本机格式导入或导出数据 (SQL Server)
  • 使用字符格式导入或导出数据 (SQL Server)
  • 使用 Unicode 本机格式导入或导出数据 (SQL Server)
  • 使用 Unicode 字符格式导入或导出数据（SQL Server）

• 指定字段和行终止符（SQL Server）

• 在批量导入期间保留 Null 或默认值 (SQL Server)

• 批量导入数据时保留标识值 (SQL Server)

• 格式化文件以导入或导出数据（SQL Server）

  • 使用 bcp 创建格式化文件（SQL Server）
  • 使用格式文件批量导入数据 (SQL Server)
  • 使用格式化文件跳过表列 (SQL Server)
  • 使用格式文件跳过数据字段 (SQL Server)
  • 使用格式文件将表列映射到数据文件字段 (SQL Server)

• 批量导入和导出 XML 文档的示例 (SQL Server)

相关内容

• 准备用于批量导出或导入的数据
• BULK INSERT （Transact-SQL）
• OPENROWSET （Transact-SQL）
• SET QUOTED_IDENTIFIER （Transact-SQL）
• sp_configure（Transact-SQL）
• sp_tableoption（Transact-SQL）
• 格式化文件以导入或导出数据（SQL Server）

获取帮助

• 关于 SQL 的想法：您对改进 SQL Server 有建议吗？
• Microsoft Q&A (SQL Server)
• DBA Stack Exchange （标签 sql-server）：提出 SQL Server 问题
• Stack Overflow （标签 sql-server）：SQL 相关开发问题的答案
• Microsoft SQL Server许可条款和信息
• 企业用户支持选项
• 更多 SQL Server 帮助和反馈

参与编辑 SQL 文档

你是否知道你可以自行编辑 SQL 内容？ 你如果这样做，不仅可以帮助改进我们的文档，还可以获得页面贡献者的殊荣。

有关详细信息，请参阅 Edit Microsoft Learn 文档。