bcp 实用工具bcp Utility

本主题适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

与以前版本的 SQL Server 相关的内容,请参阅bcp 实用工具For content related to previous versions of SQL Server, see bcp Utility.

Bcp 实用工具的最新版本,请参阅for SQL Server 的 Microsoft 命令行实用程序 14.0 For the latest version of the bcp utility, see Microsoft Command Line Utilities 14.0 for SQL Server

有关在 Linux 上使用 bcp,请参阅在 Linux 上安装 sqlcmd 和 bcpFor using bcp on Linux, see Install sqlcmd and bcp on Linux.

有关与 Azure SQL 数据仓库配合使用 bcp 的详细信息,请参阅使用 bcp 加载数据For detailed information about using bcp with Azure SQL Data Warehouse, see Load data with bcp.

Bulk c复制program 实用程序 (bcp) 的实例之间的数据大容量复制 MicrosoftMicrosoft SQL ServerSQL Server和用户指定格式数据文件。The bulk copy program utility (bcp) bulk copies data between an instance of MicrosoftMicrosoft SQL ServerSQL Server and a data file in a user-specified format. 使用 bcp 实用工具可以将大量新行导入 SQL ServerSQL Server 表,或将表数据导出到数据文件。The bcp utility can be used to import large numbers of new rows into SQL ServerSQL Server tables or to export data out of tables into data files. 除非与 queryout 选项一起使用,否则使用该实用工具不需要了解 Transact-SQLTransact-SQL知识。Except when used with the queryout option, the utility requires no knowledge of Transact-SQLTransact-SQL. 若要将数据导入表中,必须使用为该表创建的格式文件,或者必须了解表的结构以及对于该表中的列有效的数据类型。To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

主题链接图标关于语法约定用于bcp语法,请参阅TRANSACT-SQL 语法约定 (Transact SQL ).Topic link icon For the syntax conventions that are used for the bcp syntax, see Transact-SQL Syntax Conventions (Transact-SQL).

备注

如果使用 bcp 备份数据,请创建一个格式化文件来记录数据格式。If you use bcp to back up your data, create a format file to record the data format. bcp 数据文件 不包括 任何架构或格式信息,因此如果已删除表或视图并且不具备格式化文件,则可能无法导入数据。bcp data files do not include any schema or format information, so if a table or view is dropped and you do not have a format file, you may be unable to import the data.

语法Syntax
bcp [database_name.] schema.{table_name | view_name | "query"
    {in data_file | out data_file | queryout data_file | format nul}
                                                                                                         
    [-a packet_size]
    [-b batch_size]
    [-c]
    [-C { ACP | OEM | RAW | code_page } ]
    [-d database_name]
    [-e err_file]
    [-E]
    [-f format_file]
    [-F first_row]
    [-G Azure Active Directory Authentication]
    [-h"hint [,...n]"]
    [-i input_file]
    [-k]
    [-K application_intent]
    [-L last_row]
    [-m max_errors]
    [-n]
    [-N]
    [-o output_file]
    [-P password]
    [-q]
    [-r row_term]
    [-R]
    [-S [server_name[\instance_name]]
    [-t field_term]
    [-T]
    [-U login_id]
    [-v]
    [-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
    [-w]
    [-x]

参数Arguments

data_filedata_file
数据文件的完整路径。Is the full path of the data file. 将数据批量导入 SQL ServerSQL Server 时,数据文件将包含要复制到指定的表或视图中的数据。When data is bulk imported into SQL ServerSQL Server, the data file contains the data to be copied into the specified table or view. SQL ServerSQL Server中批量导出数据时,数据文件将包含从表或视图中复制的数据。When data is bulk exported from SQL ServerSQL Server, the data file contains the data copied from the table or view. 路径可以有 1 到 255 个字符。The path can have from 1 through 255 characters. 数据文件最多可包含 2^63 - 1 行。The data file can contain a maximum of 2^63 - 1 rows.

database_namedatabase_name
指定的表或视图所在数据库的名称。Is the name of the database in which the specified table or view resides. 如果未指定,则使用用户的默认数据库。If not specified, this is the default database for the user.

你也可以使用 d-显式指定数据库名称。You can also explicitly specify the database name with d-.

in data_file | out data_file | queryout data_file | format nulin data_file | out data_file | queryout data_file | format nul
指定大容量复制的方向,具体如下:Specifies the direction of the bulk copy, as follows:

  • in 从文件复制到数据库表或视图。in copies from a file into the database table or view.

  • out 从数据库表或视图复制到文件。out copies from the database table or view to a file. 如果指定了现有文件,则该文件将被覆盖。If you specify an existing file, the file is overwritten. 提取数据时,请注意 bcp 实用工具将空字符串表示为 null,而将 null 字符串表示为空字符串。When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

  • queryout 从查询中复制,仅当从查询大容量复制数据时才必须指定此选项。queryout copies from a query and must be specified only when bulk copying data from a query.

  • format 根据指定的选项(-n-c-w-N)以及表或视图的分隔符创建格式化文件。format creates a format file based on the option specified (-n, -c, -w, or -N) and the table or view delimiters. 大容量复制数据时, bcp 命令可以引用一个格式化文件,从而避免以交互方式重复输入格式信息。When bulk copying data, the bcp command can refer to a format file, which saves you from re-entering format information interactively. format 选项要求指定 -f 选项;创建 XML 格式化文件时还需要指定 -x 选项。The format option requires the -f option; creating an XML format file, also requires the -x option. 有关详细信息,请参阅 创建格式化文件 (SQL Server)For more information, see Create a Format File (SQL Server). 必须将 nul 指定为值 (format nul)。You must specify nul as the value (format nul).

    ownerowner
    表或视图的所有者的名称。Is the name of the owner of the table or view. 如果执行该操作的用户拥有指定的表或视图,则owner 是可选的。owner is optional if the user performing the operation owns the specified table or view. 如果未指定 owner,并且执行该操作的用户不是指定的表或视图的所有者,则 SQL ServerSQL Server 将返回错误消息,而且该操作将取消。If owner is not specified and the user performing the operation does not own the specified table or view, SQL ServerSQL Server returns an error message, and the operation is canceled.

" 查询 " Transact-SQLTransact-SQL返回的结果集的查询。" query " Is a Transact-SQLTransact-SQL query that returns a result set. 如果该查询返回多个结果集,则只将第一个结果集复制到数据文件,而忽略其余的结果集。If the query returns multiple result sets, only the first result set is copied to the data file; subsequent result sets are ignored. 将查询用双引号括起来,将查询中嵌入的任何内容用单引号括起来。Use double quotation marks around the query and single quotation marks around anything embedded in the query. 从查询大容量复制数据时,也必须指定queryoutqueryout must also be specified when bulk copying data from a query.

只要在执行 bcp 语句之前存储过程内引用的所有表均存在,查询就可以引用该存储过程。The query can reference a stored procedure as long as all tables referenced inside the stored procedure exist prior to executing the bcp statement. 例如,如果存储过程生成一个临时表,则 bcp 语句便会失败,因为该临时表只在运行时可用,而在语句执行时不可用。For example, if the stored procedure generates a temp table, the bcp statement fails because the temp table is available only at run time and not at statement execution time. 在这种情况下,应考虑将存储过程的结果插入表中,然后使用 bcp 将数据从表复制到数据文件中。In this case, consider inserting the results of the stored procedure into a table and then use bcp to copy the data from the table into a data file.

table_nametable_name
将数据导入 SQL ServerSQL Server (in) 时为目标表名称,将数据从 SQL ServerSQL Server 导出时 (out) 为源表名称。Is the name of the destination table when importing data into SQL ServerSQL Server (in), and the source table when exporting data from SQL ServerSQL Server (out).

view_name view_name
将数据复制到 SQL ServerSQL Server (in) 时为目标视图名称,从 SQL ServerSQL Server 中复制数据时 (out) 为源视图名称。Is the name of the destination view when copying data into SQL ServerSQL Server (in), and the source view when copying data from SQL ServerSQL Server (out). 只有其中所有列都引用同一个表的视图才能用作目标视图。Only views in which all columns refer to the same table can be used as destination views. 有关将数据复制到视图的限制的详细信息,请参阅《INSERT (Transact-SQL)》For more information on the restrictions for copying data into views, see INSERT (Transact-SQL).

-a packet_size-a packet_size
指定服务器发出或接收的每个网络数据包的字节数。Specifies the number of bytes, per network packet, sent to and from the server. 可以使用 SQL Server Management StudioSQL Server Management Studio(或 sp_configure 系统存储过程)来设置服务器配置选项。A server configuration option can be set by using SQL Server Management StudioSQL Server Management Studio (or the sp_configure system stored procedure). 但是,可以使用此选项逐个替代服务器配置选项。However, the server configuration option can be overridden on an individual basis by using this option. packet_size 的取值范围为 4096 到 65535 字节,默认为 4096 字节。packet_size can be from 4096 to 65535 bytes; the default is 4096.

增大数据包可以提高大容量复制操作的性能。Increased packet size can enhance performance of bulk-copy operations. 如果无法得到请求的较大数据包,则使用默认值。If a larger packet is requested but cannot be granted, the default is used. bcp 实用工具生成的性能统计信息可以显示所用的数据包大小。The performance statistics generated by the bcp utility show the packet size used.

-b batch_size-b batch_size
指定每批导入数据的行数。Specifies the number of rows per batch of imported data. 每个批次均作为一个单独的事务进行导入并记录,在提交之前会导入整批。Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. 默认情况下,数据文件中的所有行均作为一个批次导入。By default, all the rows in the data file are imported as one batch. 若要将行分为多个批次进行操作,请指定小于数据文件中的行数的 batch_sizeTo distribute the rows among multiple batches, specify a batch_size that is smaller than the number of rows in the data file. 如果任何批次的事务失败,则将只回滚当前批次中的插入。If the transaction for any batch fails, only insertions from the current batch are rolled back. 已经由已提交事务导入的批次不会受到将来失败的影响。Batches already imported by committed transactions are unaffected by a later failure.

不使用此选项结合-h"ROWS_PER_BATCH = ***bb"选项。Do not use this option in conjunction with the **-h "ROWS_PER_BATCH **=bb"* option.

-c-c
使用字符数据类型执行该操作。Performs the operation using a character data type. 此选项不提示输入每个字段;它使用 char 作为存储类型,没有前缀;使用 \t (制表符)作为字段分隔符,使用 \r\n (换行符)作为行终止符。This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c-w 不兼容。-c is not compatible with -w.

有关详细信息,请参阅使用字符格式导入或导出数据 (SQL Server)For more information, see Use Character Format to Import or Export Data (SQL Server).

-C { ACP | OEM | RAW | code_page } -C { ACP | OEM | RAW | code_page }
指定该数据文件中数据的代码页。Specifies the code page of the data in the data file. 仅当数据含有字符值大于 127 或小于 32 的code_pagecode_page, varcode_page列时, code_page columns with code_pageacter values greater than 127 or less than 32.code_page is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.

备注

我们建议你为格式文件中的每个列指定一个排序规则名称,除非你希望 65001 选项优先于排序规则/代码页规范。We recommend specifying a collation name for each column in a format file, except when you want the 65001 option to have priority over the collation/code page specification.

代码页值Code page value 说明Description
ACPACP ANSIANSI/Microsoft Windows (ISO 1252)。/Microsoft Windows (ISO 1252).
OEMOEM 客户端使用的默认代码页。Default code page used by the client. 未指定 -C 时使用的默认代码页。This is the default code page used if -C is not specified.
RAWRAW 不进行代码页间的转换。No conversion from one code page to another occurs. 因为不进行转换,所以这是最快的选项。This is the fastest option because no conversion occurs.
code_pagecode_page 特定的代码页编号,例如 850。Specific code page number; for example, 850.

低于 13 ( SQL Server 2016 (13.x)SQL Server 2016 (13.x)) 的版本不支持代码页 65001(UTF-8 编码)。Versions prior to version 13 ( SQL Server 2016 (13.x)SQL Server 2016 (13.x)) do not support code page 65001 (UTF-8 encoding). 版本 13 和后续版本可将 UTF-8 编码导入以前版本的 SQL ServerSQL ServerVersions beginning with 13 can import UTF-8 encoding to earlier versions of SQL ServerSQL Server.

-D database_name -d database_name
指定要连接到的数据库。Specifies the database to connect to. 默认情况下,bcp.exe 连接到用户的默认数据库。By default, bcp.exe connects to the user’s default database. 如果指定了 -d database_name 和包含三部分的名称(database_name.schema.table,作为第一个参数传递给 bcp.exe),则将发生错误,因为不能两次指定数据库名称。如果 database_name 以连字符 (-) 或正斜杠 (/) 开头,则不会在 -d 和数据库名称之间添加空格。If -d database_name and a three part name (database_name.schema.table, passed as the first parameter to bcp.exe) is specified, an error will occur because you cannot specify the database name twice.If database_name begins with a hyphen (-) or a forward slash (/), do not add a space between -d and the database name.

-e err_file-e err_file
指定错误文件的完整路径,此文件用于存储 bcp 实用工具无法从文件传输到数据库的所有行。Specifies the full path of an error file used to store any rows that the bcp utility cannot transfer from the file to the database. bcp 命令产生的错误消息将被发送到用户的工作站。Error messages from the bcp command go to the workstation of the user. 如果不使用此选项,则不会创建错误文件。If this option is not used, an error file is not created.

如果 err_file 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -eerr_file 值之间包含空格。If err_file begins with a hyphen (-) or a forward slash (/), do not include a space between -e and the err_file value.

-E -E
指定导入数据文件中的标识值用于标识列。Specifies that identity value or values in the imported data file are to be used for the identity column. 如果未指定 -E,则将忽略要导入的数据文件中此列的标识值,而且 SQL ServerSQL Server 将根据创建表期间指定的种子值和增量值自动分配唯一值。If -E is not given, the identity values for this column in the data file being imported are ignored, and SQL ServerSQL Server automatically assigns unique values based on the seed and increment values specified during table creation.

如果数据文件不包含表或视图中的标识列的值,则可使用格式化文件指定,在导入数据时应跳过表或视图中的标识列; SQL ServerSQL Server 将自动为该列分配唯一值。If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL ServerSQL Server automatically assigns unique values for the column. 有关详细信息,请参阅 DBCC CHECKIDENT (Transact-SQL)For more information, see DBCC CHECKIDENT (Transact-SQL).

-E 选项有一个特殊的权限要求。The -E option has a special permissions requirement. 有关详细信息,请参阅本主题后面的“备注”。For more information, see "Remarks" later in this topic.

-f format_file-f format_file
指定格式化文件的完整路径。Specifies the full path of a format file. 此选项的含义取决于使用它的环境,具体如下:The meaning of this option depends on the environment in which it is used, as follows:

  • 如果 -fformat 选项一起使用,则将为指定的表或视图创建指定的 format_fileIf -f is used with the format option, the specified format_file is created for the specified table or view. 若要创建 XML 格式化文件,请同时指定 -x 选项。To create an XML format file, also specify the -x option. 有关详细信息,请参阅 创建格式化文件 (SQL Server)For more information, see Create a Format File (SQL Server).

  • 如果与 inout 选项一起使用,则 -f 需要一个现有的格式化文件。If used with the in or out option, -f requires an existing format file.

    备注

    inout 选项一起使用时,格式化文件是可选的。Using a format file in with the in or out option is optional. 如果没有 -f 选项,则在未指定 -n-c-w-N 时,该命令将提示输入格式信息,并允许你将响应保存在格式化文件(默认文件名为 Bcp.fmt)中。In the absence of the -f option, if -n, -c, -w, or -N is not specified, the command prompts for format information and lets you save your responses in a format file (whose default file name is Bcp.fmt).

    如果 format_file 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -fformat_file 值之间包含空格。If format_file begins with a hyphen (-) or a forward slash (/), do not include a space between -f and the format_file value.

-F first_row-F first_row
指定要从表中导出或从数据文件导入的第一行的编号。Specifies the number of the first row to export from a table or import from a data file. 此参数的值应大于 (>) 0,小于 (<) 或等于 (=) 总行数。This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. 如果未指定此参数,则默认为文件的第一行。In the absence of this parameter, the default is the first row of the file.

first_row 可以是一个最大为 2^63-1 的正整数值。first_row can be a positive integer with a value up to 2^63-1. -F first_row 的值从 1 开始。-F first_row is 1-based.

-G-G
此开关用于客户端连接到 Azure SQL 数据库或 Azure SQL 数据仓库时指定用户进行身份验证使用 Azure Active Directory 身份验证。This switch is used by the client when connecting to Azure SQL Database or Azure SQL Data Warehouse to specify that the user be authenticated using Azure Active Directory authentication. -G 开关需要版本 14.0.3008.27 或更高版本The -G switch requires version 14.0.3008.27 or later. 若要确定你的版本,请执行 bcp-v。To determine your version, execute bcp -v. 有关详细信息,请参阅使用 Azure Active Directory 身份验证进行身份验证与 SQL 数据库或 SQL 数据仓库For more information, see Use Azure Active Directory Authentication for authentication with SQL Database or SQL Data Warehouse.

提示

若要查看你的 bcp 版本是否包括 Azure Active Directory 身份验证 (AAD) 类型的支持bcp- (bcp<空间 ><dash ><dash >),并验证你看到-G 在列表中可用的自变量。To check if your version of bcp includes support for Azure Active Directory Authentication (AAD) type bcp -- (bcp<space><dash><dash>) and verify that you see -G in the list of available arguments.

  • Azure Active Directory 用户名和密码:Azure Active Directory Username and Password:

    当你想要使用 Azure Active Directory 用户名和密码时,可以提供 -G 选项,也可以通过提供 -U 选项和 -P 选项来使用用户名和密码。When you want to use an Azure Active Directory user name and password, you can provide the -G option and also use the user name and password by providing the -U and -P options.

    下面的示例将导出的数据使用 Azure AD 的用户名和密码,用户和密码是一个 AAD 凭据。The following example exports data using Azure AD Username and Password where user and password is an AAD credential. 此示例导出表bcptest从数据库testdb从 Azure 服务器aadserver.database.windows.net,并将数据存储在文件中c:\last\data1.dat:The example exports table bcptest from database testdb from Azure server aadserver.database.windows.net and stores the data in file c:\last\data1.dat:

    bcp bcptest out "c:\last\data1.dat" -c -t -S aadserver.database.windows.net -d testdb -G -U alice@aadtest.onmicrosoft.com -P xxxxx
    

    下面的示例导入数据使用 Azure AD 的用户名和密码,用户和密码是一个 AAD 凭据。The following example imports data using Azure AD Username and Password where user and password is an AAD credential. 此示例从文件导入数据c:\last\data1.dat到表bcptest数据库testdbAzure 服务器上aadserver.database.windows.net使用 Azure AD 用户/密码:The example imports data from file c:\last\data1.dat into table bcptest for database testdb on Azure server aadserver.database.windows.net using Azure AD User/Password:

    bcp bcptest in "c:\last\data1.dat" -c -t -S aadserver.database.windows.net -d testdb -G -U alice@aadtest.onmicrosoft.com -P xxxxx
    
  • Azure Active Directory 集成Azure Active Directory Integrated

    对于 Azure Active Directory 集成身份验证,提供-G而无需用户名或密码的选项。For Azure Active Directory Integrated authentication, provide the -G option without a user name or password. 此配置假定与 Azure AD 联合的当前 Windows 用户帐户 (帐户下运行 bcp 命令):This configuration assumes that the current Windows user account (the account the bcp command is running under) is federated with Azure AD:

    下面的示例将使用 Azure AD 集成的帐户的数据导出。The following example exports data using Azure AD Integrated account. 此示例导出表bcptest从数据库testdb使用从 Azure 服务器的 Azure AD 集成aadserver.database.windows.net,并将数据存储在文件中c:\last\data2.dat:The example exports table bcptest from database testdb using Azure AD Integrated from Azure server aadserver.database.windows.net and stores the data in file c:\last\data2.dat:

    bcp bcptest out "c:\last\data2.dat" -S aadserver.database.windows.net -d testdb -G -c -t
    

    下面的示例将使用 Azure AD 集成的验证的数据导入此示例从文件导入数据c:\last\data2.txt到表bcptest数据库testdbAzure 服务器上aadserver.database.windows.net使用 Azure AD 集成的身份验证:The following example imports data using Azure AD Integrated auth. The example imports data from file c:\last\data2.txt into table bcptest for database testdb on Azure server aadserver.database.windows.net using Azure AD Integrated auth:

    bcp bcptest in "c:\last\data2.dat" -S aadserver.database.windows.net -d testdb -G -c -t
    

-h "load hints[ ,... n]" 指定向表或视图中批量导入数据时要用到的提示(一个或多个)。-h "load hints[ ,... n]" Specifies the hint or hints to be used during a bulk import of data into a table or view.

  • ORDER(column[ASC | DESC] [,...n])ORDER(column[ASC | DESC] [,...n])
    数据文件中的数据排序次序。The sort order of the data in the data file. 如果根据表中的聚集索引(如果有)对要导入的数据排序,则可提高批量导入的性能。Bulk import performance is improved if the data being imported is sorted according to the clustered index on the table, if any. 如果数据文件以不同的次序(即不同于聚集索引键的次序)排序,或者表中不存在任何聚集索引,则将忽略 ORDER 子句。If the data file is sorted in a different order, that is other than the order of a clustered index key, or if there is no clustered index on the table, the ORDER clause is ignored. 提供的列名必须是目标表中有效的列名。The column names supplied must be valid column names in the destination table. 默认情况下, bcp 假定数据文件没有排序。By default, bcp assumes the data file is unordered. 对于经过优化的批量导入, SQL ServerSQL Server 还将验证导入的数据是否已排序。For optimized bulk import, SQL ServerSQL Server also validates that the imported data is sorted.

  • ROWS_PER_BATCH = bbROWS_PER_BATCH = bb
    每批数据的行数(即 bb)。Number of rows of data per batch (as bb). 在未指定 -b 时使用,这将导致整个数据文件作为单个事务发送到服务器。Used when -b is not specified, resulting in the entire data file being sent to the server as a single transaction. 服务器根据 bb值优化大容量加载。The server optimizes the bulk load according to the value bb. 默认情况下,ROWS_PER_BATCH 是未知的。By default, ROWS_PER_BATCH is unknown.

  • KILOBYTES_PER_BATCH = ccKILOBYTES_PER_BATCH = cc
    每批的以千字节计数的近似数据量(即 cc)。Approximate number of kilobytes of data per batch (as cc). 默认情况下,KILOBYTES_PER_BATCH 是未知的。By default, KILOBYTES_PER_BATCH is unknown.

  • TABLOCKTABLOCK
    指定在大容量加载操作期间获取大容量更新表级别的锁;否则,获取行级别的锁。Specifies that a bulk update table-level lock is acquired for the duration of the bulk load operation; otherwise, a row-level lock is acquired. 由于在大容量复制操作期间拥有锁可以减少表中的锁争夺,所以此提示可显著提高性能。This hint significantly improves performance because holding a lock for the duration of the bulk-copy operation reduces lock contention on the table. 如果表没有索引并且指定了 TABLOCK ,则该表可以同时由多个客户端加载。A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. 默认情况下,锁定行为由表选项 table lock on bulk load决定。By default, locking behavior is determined by the table option table lock on bulk load.

    备注

    如果目标表是聚集列存储索引,则多个并发客户端不需要 TABLOCK 提示就能加载,因为将在索引中为每个并发线程分配单独的行组,并在该行组中加载数据。If the target table is clustered columnstore index, TABLOCK hint is not required for loading by multiple concurrent clients because each concurrent thread is assigned a separate rowgroup within the index and loads data into it. 有关详细信息,请参阅列存储索引概念主题,Please refer to columnstore index conceptual topics for details,

    CHECK_CONSTRAINTSCHECK_CONSTRAINTS
    指定在批量导入操作期间,必须检查所有对目标表或视图的约束。Specifies that all constraints on the target table or view must be checked during the bulk-import operation. 如果没有 CHECK_CONSTRAINTS 提示,则忽略所有 CHECK 和 FOREIGN KEY 约束;操作完成后,对表的约束将被标记为不可信。Without the CHECK_CONSTRAINTS hint, any CHECK and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted.

    备注

    始终强制使用 UNIQUE、PRIMARY KEY 和 NOT NULL 约束。UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced.

    在某些时候,需要检查整个表的约束。At some point, you will need to check the constraints on the entire table. 如果在批量导入操作之前表为非空状态,则重新验证约束的开销可能超过将 CHECK 约束应用于增量数据的开销。If the table was nonempty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK constraints to the incremental data. 因此,建议您在正常情况下,在进行增量式批量导入时启用约束检查。Therefore, we recommend that normally you enable constraint checking during an incremental bulk import.

    当输入数据包含违反约束的行时,您可能希望禁用约束(默认行为)。A situation in which you might want constraints disabled (the default behavior) is if the input data contains rows that violate constraints. 如果禁用 CHECK 约束,您可以导入数据,然后使用 Transact-SQLTransact-SQL 语句删除无效数据。With CHECK constraints disabled, you can import the data and then use Transact-SQLTransact-SQL statements to remove data that is not valid.

    备注

    bcp 现在会强制执行数据验证和数据检查,这样,在对数据文件中的无效数据执行脚本时,可能会导致脚本失败。bcp now enforces data validation and data checks that might cause scripts to fail if they are executed on invalid data in a data file.

    备注

    -m max_errors 开关不适用于约束检查。The -m max_errors switch does not apply to constraint checking.

  • FIRE_TRIGGERSFIRE_TRIGGERS
    in 参数一同指定,在目标表中定义的任何插入触发器都将在大容量复制操作期间运行。Specified with the in argument, any insert triggers defined on the destination table will run during the bulk-copy operation. 如果未指定 FIRE_TRIGGERS,将不运行任何插入触发器。If FIRE_TRIGGERS is not specified, no insert triggers will run. 对于 outqueryoutformat 参数,将忽略 FIRE_TRIGGERS。FIRE_TRIGGERS is ignored for the out, queryout, and format arguments.

    -i input_file-i input_file
    指定响应文件的名称,其中包含在交互模式(未指定-n-c-w-N )下执行大容量复制时,对每个数据字段的命令提示问题所做出的响应。Specifies the name of a response file, containing the responses to the command prompt questions for each data field when a bulk copy is being performed using interactive mode (-n, -c, -w, or -N not specified).

    如果 input_file 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -iinput_file 值之间包含空格。If input_file begins with a hyphen (-) or a forward slash (/), do not include a space between -i and the input_file value.

    -k-k
    指定在操作过程中空列应该保留 null 值,而不是所插入列的任何默认值。Specifies that empty columns should retain a null value during the operation, rather than have any default values for the columns inserted. 有关详细信息,请参阅在批量导入期间保留 Null 或使用默认值 (SQL Server)For more information, see Keep Nulls or Use Default Values During Bulk Import (SQL Server).

    -K application_intent -K application_intent
    连接到服务器时声明应用程序工作负荷类型。Declares the application workload type when connecting to a server. 唯一可能的值是 ReadOnlyThe only value that is possible is ReadOnly. 如果未指定 -K,bcp 实用工具将不支持连接到 Always On 可用性组中的辅助副本。If -K is not specified, the bcp utility will not support connectivity to a secondary replica in an Always On availability group. 有关详细信息,请参阅 活动辅助副本:可读辅助副本(AlwaysOn 可用性组)概念。For more information, see Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

    -L last_row-L last_row
    指定要从表中导出或从数据文件中导入的最后一行的编号。Specifies the number of the last row to export from a table or import from a data file. 此参数的值应大于 (>) 0,小于 (<) 或等于 (=) 最后一行的编号。This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the number of the last row. 如果未指定此参数,则默认为文件的最后一行。In the absence of this parameter, the default is the last row of the file.

    last_row 可以是一个最大为 2^63-1 的正整数值。last_row can be a positive integer with a value up to 2^63-1.

-m max_errors-m max_errors
指定取消 bcp 操作之前可能出现的语法错误的最大数目。Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. 语法错误是指将数据转换为目标数据类型时的错误。A syntax error implies a data conversion error to the target data type. max_errors 总数不包括只能在服务器中检测到的错误,如约束冲突。The max_errors total excludes any errors that can be detected only at the server, such as constraint violations.

无法由 bcp 实用工具复制的行将被忽略,并计为一个错误。A row that cannot be copied by the bcp utility is ignored and is counted as one error. 如果未包括此选项,则默认值为 10。If this option is not included, the default is 10.

备注

-m 选项也不适用于转换 moneybigint 数据类型。The -m option also does not apply to converting the money or bigint data types.

-n
使用数据的本机(数据库)数据类型执行大容量复制操作。Performs the bulk-copy operation using the native (database) data types of the data. 此选项不提示输入每个字段,它将使用本机值。This option does not prompt for each field; it uses the native values.

有关详细信息,请参阅使用本机格式导入或导出数据 (SQL Server)For more information, see Use Native Format to Import or Export Data (SQL Server).

-N-N
执行大容量复制操作时,对非字符数据使用本机(数据库)数据类型的数据,对字符数据使用 Unicode 字符。Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. 此选项是 -w 选项的一个替代选项,并具有更高的性能。此选项主要用于通过数据文件将数据从 SQL ServerSQL Server 的一个实例传送到另一个实例。This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL ServerSQL Server to another using a data file. 此选项不提示输入每个字段。It does not prompt for each field. 如果要传送包含 ANSI 扩展字符的数据,并希望利用本机模式的性能优势,则可使用此选项。Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode.

有关详细信息信息,请参阅使用 Unicode 本机格式导入或导出数据 (SQL Server)For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).

通过使用 bcp.exe 和 -N 来导出数据后又将数据导入到同一表架构时,如果存在固定长度的非 Unicode 字符列(例如 char(10)),系统可能会显示截断警告。If you export and then import data to the same table schema by using bcp.exe with -N, you might see a truncation warning if there is a fixed length, non-Unicode character column (for example, char(10)).

可忽略该警告。The warning can be ignored. 解决此警告的一个方法是使用 -n 来替代 -N知识。One way to resolve this warning is to use -n instead of -N.

-o output_file-o output_file
指定文件名称,该文件用于接收从命令提示符重定向来的输出。Specifies the name of a file that receives output redirected from the command prompt.

如果 output_file 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -ooutput_file 值之间包含空格。If output_file begins with a hyphen (-) or a forward slash (/), do not include a space between -o and the output_file value.

-P password-P password
指定登录 ID 的密码。Specifies the password for the login ID. 如果未使用此选项, bcp 命令将提示输入密码。If this option is not used, the bcp command prompts for a password. 如果在命令提示符的末尾使用此选项,但不提供密码,则 bcp 将使用默认密码 (NULL)。If this option is used at the end of the command prompt without a password, bcp uses the default password (NULL).

重要

不要使用空密码。Do not use a blank password. 请使用强密码。Use a strong password.

若要屏蔽密码,请不要指定 -P 选项和 -U 选项。To mask your password, do not specify the -P option along with the -U option. 而应在指定 bcp 以及 -U 选项和其他开关(不指定 -P)之后按 Enter,这时命令会提示输入密码。Instead, after specifying bcp along with the -U option and other switches (do not specify -P), press ENTER, and the command will prompt you for a password. 这种方法可以确保输入密码时对其屏蔽。This method ensures that your password will be masked when it is entered.

如果 password 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -P 与 password 值之间添加空格。If password begins with a hyphen (-) or a forward slash (/), do not add a space between -P and the password value.

-q-q
bcp 实用工具和 SQL ServerSQL Server实例之间的连接中,执行 SET QUOTED_IDENTIFIERS ON 语句。Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL ServerSQL Server. 使用此选项可以指定包含空格或单引号的数据库、所有者、表或视图的名称。Use this option to specify a database, owner, table, or view name that contains a space or a single quotation mark. 将由三部分组成的整个表名或视图名用英文双引号 ("") 引起来。Enclose the entire three-part table or view name in quotation marks ("").

若要指定包含空格或单引号的数据库名称,必须使用 –q 选项。To specify a database name that contains a space or single quotation mark, you must use the –q option.

-q 不适用于传递到 -d的值。-q does not apply to values passed to -d.

有关详细信息,请参阅本主题后面的 备注For more information, see Remarks, later in this topic.

-r row_term-r row_term
指定行终止符。Specifies the row terminator. 默认值是 \n (换行符)。The default is \n (newline character). 使用此参数可替代默认行终止符。Use this parameter to override the default row terminator. 有关详细信息,请参阅指定字段终止符和行终止符 (SQL Server)For more information, see Specify Field and Row Terminators (SQL Server).

如果您在 bcp.exe 命令中以十六进制表示法指定行终止符,则该值将在 0x00 处截断。If you specify the row terminator in hexadecimal notation in a bcp.exe command, the value will be truncated at 0x00. 例如,如果您指定 0x410041,则将使用 0x41。For example, if you specify 0x410041, 0x41 will be used.

如果 row_term 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -rrow_term 值之间包含空格。If row_term begins with a hyphen (-) or a forward slash (/), do not include a space between -r and the row_term value.

-R-R
指定使用客户端计算机区域设置中定义的区域格式,将货币、日期和时间数据大容量复制到 SQL ServerSQL Server 中。Specifies that currency, date, and time data is bulk copied into SQL ServerSQL Server using the regional format defined for the locale setting of the client computer. 默认情况下,将忽略区域设置。By default, regional settings are ignored.

-S server_name [\instance_name] 指定的实例 SQL ServerSQL Server来进行连接。-S server_name [\instance_name] Specifies the instance of SQL ServerSQL Server to which to connect. 如果未指定服务器,则 bcp 实用工具将连接到本地计算机上的默认 SQL ServerSQL Server 实例。If no server is specified, the bcp utility connects to the default instance of SQL ServerSQL Server on the local computer. 如果从网络或本地命名实例上的远程计算机中运行 bcp 命令,则必须使用此选项。This option is required when a bcp command is run from a remote computer on the network or a local named instance. 若要连接到服务器上的 SQL ServerSQL Server 默认实例,请仅指定 server_nameTo connect to the default instance of SQL ServerSQL Server on a server, specify only server_name. 若要连接到的命名实例 SQL ServerSQL Server,指定server_name\instance_nameTo connect to a named instance of SQL ServerSQL Server, specify server_name\instance_name.

-t field_term-t field_term
指定字段终止符。Specifies the field terminator. 默认的字段终止符是 \t (制表符)。The default is \t (tab character). 使用此参数可以替代默认字段终止符。Use this parameter to override the default field terminator. 有关详细信息,请参阅指定字段终止符和行终止符 (SQL Server)For more information, see Specify Field and Row Terminators (SQL Server).

如果您在 bcp.exe 命令中以十六进制表示法指定字段终止符,则该值将在 0x00 处截断。If you specify the field terminator in hexadecimal notation in a bcp.exe command, the value will be truncated at 0x00. 例如,如果您指定 0x410041,则将使用 0x41。For example, if you specify 0x410041, 0x41 will be used.

如果 field_term 以连字符 (-) 或正斜杠 (/) 开头,则不要在 -tfield_term 值之间包含空格。If field_term begins with a hyphen (-) or a forward slash (/), do not include a space between -t and the field_term value.

-T-T
指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL ServerSQL ServerSpecifies that the bcp utility connects to SQL ServerSQL Server with a trusted connection using integrated security. 不需要网络用户的安全凭据 login_id 和 password。The security credentials of the network user, login_id, and password are not required. 如果你未指定 -T ,则需要指定 -U-P 才能成功登录。If –T is not specified, you need to specify –U and –P to successfully log in.

重要

如果 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL ServerSQL Server,则使用的是 -T 选项(可信连接),而不是用户名和密码的组合。When the bcp utility is connecting to SQL ServerSQL Server with a trusted connection using integrated security, use the -T option (trusted connection) instead of the user name and password combination. bcp 实用工具连接到 SQL 数据库或 SQL 数据仓库时,不支持使用 Windows 身份验证或 Azure Active Directory 身份验证。When the bcp utility is connecting to SQL Database or SQL Data Warehouse, using Windows authentication or Azure Active Directory authentication is not supported. 使用 -U-P 选项。Use the -U and -P options.

-U login_id-U login_id
指定用于连接到 SQL ServerSQL Server 的登录 ID。Specifies the login ID used to connect to SQL ServerSQL Server.

重要

如果 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL ServerSQL Server,则使用的是 -T 选项(可信连接),而不是用户名和密码的组合。When the bcp utility is connecting to SQL ServerSQL Server with a trusted connection using integrated security, use the -T option (trusted connection) instead of the user name and password combination. bcp 实用工具连接到 SQL 数据库或 SQL 数据仓库时,不支持使用 Windows 身份验证或 Azure Active Directory 身份验证。When the bcp utility is connecting to SQL Database or SQL Data Warehouse, using Windows authentication or Azure Active Directory authentication is not supported. 使用 -U-P 选项。Use the -U and -P options.

-v-v
报告 bcp 实用工具的版本号和版权信息。Reports the bcp utility version number and copyright.

-V (80 | 90 | 100 | 110 | 120 | 130 )-V (80 | 90 | 100 | 110 | 120 | 130 )
使用 SQL ServerSQL Server早期版本中的数据类型执行大容量复制操作。Performs the bulk-copy operation using data types from an earlier version of SQL ServerSQL Server. 此选项并不提示输入每个字段,它将使用默认值。This option does not prompt for each field; it uses the default values.

80 = 80 = SQL Server 2000 (8.x)SQL Server 2000 (8.x)

90 = 90 = SQL Server 2005SQL Server 2005

100 = SQL Server 2008SQL Server 2008100 = SQL Server 2008SQL Server 2008 and SQL Server 2008 R2SQL Server 2008 R2

110 = 110 = SQL Server 2012 (11.x)SQL Server 2012 (11.x)

120 = 120 = SQL Server 2014 (12.x)SQL Server 2014 (12.x)

130 = 130 = SQL Server 2016 (13.x)SQL Server 2016 (13.x)

例如,若要为 SQL Server 2000 (8.x)SQL Server 2000 (8.x) 不支持、但是在较高版本的 SQL ServerSQL Server 中引入的类型生成数据,请使用 -V80 选项。For example, to generate data for types not supported by SQL Server 2000 (8.x)SQL Server 2000 (8.x), but were introduced in later versions of SQL ServerSQL Server, use the -V80 option.

有关详细信息,请参阅 导入来自早期版本的 SQL Server 的本机格式数据和字符格式数据For more information, see Import Native and Character Format Data from Earlier Versions of SQL Server.

-w-w
使用 Unicode 字符执行大容量复制操作。Performs the bulk copy operation using Unicode characters. 此选项不提示输入每个字段;它使用 nchar 作为存储类型,不带前缀;使用 \t (制表符)作为字段分隔符,使用 \n (换行符)作为行终止符。This option does not prompt for each field; it uses nchar as the storage type, no prefixes, \t (tab character) as the field separator, and \n (newline character) as the row terminator. -w-c不兼容。-w is not compatible with -c.

有关详细信息,请参阅 使用 Unicode 字符格式导入或导出数据 (SQL Server)For more information, see Use Unicode Character Format to Import or Export Data (SQL Server).

-x-x
结合使用 format-f format_file 选项,可生成基于 XML 的格式化文件,而不是默认的非 XML 格式化文件。Used with the format and -f format_file options, generates an XML-based format file instead of the default non-XML format file. 在导入或导出数据时,-x 不起作用。The -x does not work when importing or exporting data. 如果不与 format-f format_file 一起使用,则将生成错误。It generates an error if used without both format and -f format_file.

备注Remarks

使用 bcp 工具时,将安装 MicrosoftMicrosoft SQL Server 2017SQL Server 2017 13.0 客户端。The bcp 13.0 client is installed when you install MicrosoftMicrosoft SQL Server 2017SQL Server 2017 tools. 如果同时安装了 SQL Server 2017SQL Server 2017 和早期版本 SQL ServerSQL Server的工具,你所使用的可能是早期版本的 bcp 客户端,而不是 bcp 13.0 客户端,具体情况取决于 PATH 环境变量的值。If tools are installed for both SQL Server 2017SQL Server 2017 and an earlier version of SQL ServerSQL Server, depending on the order of values of the PATH environment variable, you might be using the earlier bcp client instead of the bcp 13.0 client. 此环境变量定义 Windows 用于搜索可执行文件的目录集。This environment variable defines the set of directories used by Windows to search for executable files. 若要确定当前所使用的版本,请在 Windows 命令提示符下运行 bcp /v 命令。To discover which version you are using, run the bcp /v command at the Windows Command Prompt. 有关如何在 PATH 环境变量中设置命令路径的信息,请参阅 Windows 帮助。For information about how to set the command path in the PATH environment variable, see Windows Help.

bcp 实用工具还可以与 Microsoft SQL Server 2016 功能包分开下载。The bcp utility can also be downloaded separately from the Microsoft SQL Server 2016 Feature Pack. 选择 ENU\x64\MsSqlCmdLnUtils.msiENU\x86\MsSqlCmdLnUtils.msiSelect either ENU\x64\MsSqlCmdLnUtils.msi or ENU\x86\MsSqlCmdLnUtils.msi.

只有当 SQL ServerSQL Server 工具和 SQL ServerSQL Server Native Client 一起安装后,才支持 XML 格式化文件。XML format files are only supported when SQL ServerSQL Server tools are installed together with SQL ServerSQL Server Native Client.

有关在何处查找或如何运行 bcp 实用工具的信息以及有关命令提示实用工具语法约定的信息,请参阅《Command Prompt Utility Reference (Database Engine)》(命令提示实用工具参考(数据库引擎))。For information about where to find or how to run the bcp utility and about the command prompt utilities syntax conventions, see Command Prompt Utility Reference (Database Engine).

有关准备数据以进行批量导入或导出操作的信息,请参阅准备用于批量导出或导入的数据 (SQL Server)For information on preparing data for bulk import or export operations, see Prepare Data for Bulk Export or Import (SQL Server).

有关何时在事务日志中记录由批量导入执行的行插入操作的信息,请参阅《Prerequisites for Minimal Logging in Bulk Import》(批量导入的最小日志记录的先决条件)。For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import.

本机数据文件支持Native Data File Support

SQL Server 2017SQL Server 2017中, bcp 实用工具支持与 SQL Server 2000 (8.x)SQL Server 2000 (8.x)SQL Server 2005SQL Server 2005SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2SQL Server 2012 (11.x)SQL Server 2012 (11.x)兼容的本机数据文件。In SQL Server 2017SQL Server 2017, the bcp utility supports native data files compatible with SQL Server 2000 (8.x)SQL Server 2000 (8.x), SQL Server 2005SQL Server 2005, SQL Server 2008SQL Server 2008, SQL Server 2008 R2SQL Server 2008 R2, and SQL Server 2012 (11.x)SQL Server 2012 (11.x).

计算列和 timestamp 列Computed Columns and timestamp Columns

数据文件中针对计算列或 timestamp 列导入的值将被忽略, SQL ServerSQL Server 将自动分配该列的值。Values in the data file being imported for computed or timestamp columns are ignored, and SQL ServerSQL Server automatically assigns values. 如果数据文件不包含表中的计算列或 timestamp 列的值,则可使用格式化文件指定应在导入数据时忽略表中的计算列或 timestamp 列; SQL ServerSQL Server 将自动分配该列的值。If the data file does not contain values for the computed or timestamp columns in the table, use a format file to specify that the computed or timestamp columns in the table should be skipped when importing data; SQL ServerSQL Server automatically assigns values for the column.

计算列和 timestamp 列会照常从 SQL ServerSQL Server 大容量复制到数据文件中。Computed and timestamp columns are bulk copied from SQL ServerSQL Server to a data file as usual.

指定包含空格或引号的标识符Specifying Identifiers That Contain Spaces or Quotation Marks

SQL ServerSQL Server 标识符可以包含嵌入的空格和引号等字符。identifiers can include characters such as embedded spaces and quotation marks. 此类标识符必须按以下方式处理:Such identifiers must be treated as follows:

  • 如果在命令指示符处指定的标识符或文件名包含空格或引号,则需用英文双引号 ("") 将该标识符引起来。When you specify an identifier or file name that includes a space or quotation mark at the command prompt, enclose the identifier in quotation marks ("").

    例如,下面的 bcp out 命令创建了一个名为 Currency Types.dat的数据文件:For example, the following bcp out command creates a data file named Currency Types.dat:

    bcp AdventureWorks2012.Sales.Currency out "Currency Types.dat" -T -c  
    
  • 若要指定包含空格或引号的数据库名称,必须使用 -q 选项。To specify a database name that contains a space or quotation mark, you must use the -q option.

  • 对于包含嵌入空格或引号的所有者、表或视图的名称,可以执行以下任一操作:For owner, table, or view names that contain embedded spaces or quotation marks, you can either:

    • 指定 -q 选项,或者Specify the -q option, or

    • 将所有者、表或视图的名称括在方括号 ([]) 中,并用引号引起来。Enclose the owner, table, or view name in brackets ([]) inside the quotation marks.

数据验证Data Validation

bcp 现在会强制执行数据验证和数据检查,这样,在对数据文件中的无效数据执行脚本时,可能会导致脚本失败。bcp now enforces data validation and data checks that might cause scripts to fail if they are executed on invalid data in a data file. 例如, bcp 现在可以验证:For example, bcp now verifies that:

  • floatreal 数据类型的本机表示形式是否有效。The native representation of float or real data types are valid.

  • Unicode 数据的字节数是否为偶数。Unicode data has an even-byte length.

    可以在早期版本的 SQL ServerSQL Server 中批量导入的无效数据类型现在可能无法加载。在早期版本中,仅当客户端尝试访问无效数据时才出现失败。Forms of invalid data that could be bulk imported in earlier versions of SQL ServerSQL Server might fail to load now; whereas, in earlier versions, the failure did not occur until a client tried to access the invalid data. 在大容量加载后查询数据时,添加的验证可最大限度地减少警告。The added validation minimizes surprises when querying the data after bulk load.

批量导出或导入 SQLXML 文档Bulk Exporting or Importing SQLXML Documents

若要批量导出或导入 SQLXML 数据,请在格式化文件中使用下列数据类型之一。To bulk export or import SQLXML data, use one of the following data types in your format file.

数据类型Data type 效果Effect
SQLCHAR 或 SQLVARYCHARSQLCHAR or SQLVARYCHAR 在客户端代码页或排序规则隐含的代码页中发送数据。The data is sent in the client code page or in the code page implied by the collation). 其效果和指定 -c 开关而不指定格式化文件相同。The effect is the same as specifying the -c switch without specifying a format file.
SQLNCHAR 或 SQLNVARCHARSQLNCHAR or SQLNVARCHAR 以 Unicode 格式发送数据。The data is sent as Unicode. 其效果和指定 -w 开关而不指定格式化文件相同。The effect is the same as specifying the -w switch without specifying a format file.
SQLBINARY 或 SQLVARYBINSQLBINARY or SQLVARYBIN 不经任何转换即发送数据。The data is sent without any conversion.

权限Permissions

bcp out 操作要求对源表有 SELECT 权限。A bcp out operation requires SELECT permission on the source table.

bcp in 操作要求至少对目标表有 SELECT/INSERT 权限。A bcp in operation minimally requires SELECT/INSERT permissions on the target table. 此外,如果下列任一条件成立,则要求拥有 ALTER TABLE 权限:In addition, ALTER TABLE permission is required if any of the following is true:

  • 存在约束,但没有指定 CHECK_CONSTRAINTS 提示。Constraints exist and the CHECK_CONSTRAINTS hint is not specified.

    备注

    禁用约束是默认行为。Disabling constraints is the default behavior. 若要显式启用约束,请使用 -h 选项和 CHECK_CONSTRAINTS 提示。To enable constraints explicitly, use the -h option with the CHECK_CONSTRAINTS hint.

  • 存在触发器,但没有指定 FIRE_TRIGGER 提示。Triggers exist and the FIRE_TRIGGER hint is not specified.

    备注

    默认情况下,不激发触发器。By default, triggers are not fired. 若要显式激发触发器,请使用 -h 选项和 FIRE_TRIGGERS 提示。To fire triggers explicitly, use the -h option with the FIRE_TRIGGERS hint.

  • 可以使用 -E 选项从数据文件导入标识值。You use the -E option to import identity values from a data file.

备注

要求对目标表具有 ALTER TABLE 权限是 SQL Server 2005SQL Server 2005 的新要求。Requiring ALTER TABLE permission on the target table was new in SQL Server 2005SQL Server 2005. 如果用户帐户不具有对目标表的 ALTER TABLE 权限,这项新要求有可能导致不强制使用触发器和约束检查的 bcp 脚本失败。This new requirement might cause bcp scripts that do not enforce triggers and constraint checks to fail if the user account lacks ALTER table permissions for the target table.

字符模式 (-c) 和本机模式 (-n) 最佳做法Character Mode (-c) and Native Mode (-n) Best Practices

本节提供与字符模式 (-c) 和本机模式 (-n) 有关的一些建议。This section has recommendations for to character mode (-c) and native mode (-n).

  • (管理员/用户)应尽可能使用本机格式 (-n) 以避免分隔符问题。(Administrator/User) When possible, use native format (-n) to avoid the separator issue. 使用本机格式可以使用 SQL ServerSQL Server 进行导出和导入。Use the native format to export and import using SQL ServerSQL Server. 如果数据将导入到非 SQL ServerSQL Server 数据库,则使用 -c 或 -w 选项从 SQL ServerSQL Server 导出数据。Export data from SQL ServerSQL Server using the -c or -w option if the data will be imported to a non- SQL ServerSQL Server database.

  • (管理员)在使用 BCP OUT 时验证数据。(Administrator) Verify data when using BCP OUT. 例如,在您使用 BCP OUT、BCP IN,然后又使用 BCP OUT 时,请验证数据正确导出,并且终止符值未用作某个数据值的一部分。For example, when you use BCP OUT, BCP IN, and then BCP OUT verify that the data is properly exported and the terminator values are not used as part of some data value. 请考虑使用随机的十六进制值覆盖默认的终止符(使用 -t 和 -r 选项),以便避免终止符值和数据值之间的冲突。Please consider overriding the default terminators (using -t and -r options) with random hexadecimal values to avoid conflicts between terminator values and data values.

  • (用户)使用长且唯一的终止符(任意字节或字符序列)可以最大程度减少与实际字符串值冲突的可能性。(User) Use a long and unique terminator (any sequence of bytes or characters) to minimize the possibility of a conflict with the actual string value. 这可以通过使用 -t 和 -r 选项实现。This can be done by using the -t and -r options.

示例Examples

本部分包含以下示例:This section contains the following examples:

  • A.A. 标识 bcp 实用工具版本Identify bcp utility version

  • B.B. 将表行复制到数据文件中(使用可信连接)Copying table rows into a data file (with a trusted connection)

  • C.C. 将表行复制到数据文件中(使用混合模式身份验证)Copying table rows into a data file (with Mixed-mode Authentication)

  • D.D. 将文件中的数据复制到表中Copying data from a file to a table

  • E.E. 将特定的列复制到数据文件中Copying a specific column into a data file

  • F.F. 将特定的行复制到数据文件中Copying a specific row into a data file

  • G.G. 将查询中的数据复制到数据文件中Copying data from a query to a data file

  • H.H. 创建格式化文件Creating format files

  • I.I. 使用格式化文件进行 bcp批量导入Using a format file to bulk import with bcp

示例测试条件Example Test Conditions

以下示例使用 SQL Server(从 2016 开始)和 Azure SQL 数据库的 WideWorldImporters 示例数据库。The examples below make use of the WideWorldImporters sample database for SQL Server (starting 2016) and Azure SQL Database. WideWorldImporters 可以从 https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0下载。WideWorldImporters can be downloaded from https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0. 有关用于还原示例数据库的语法,请参阅 RESTORE (Transact-SQL)See RESTORE (Transact-SQL) for the syntax to restore the sample database. 除了另行指定的位置,该示例假定你使用 Windows 身份验证,并且与运行 bcp 命令所针对的服务器实例之间具有可信连接。Except where specified otherwise, the examples assume that you are using Windows Authentication and have a trusted connection to the server instance on which you are running the bcp command. 会在许多示例中使用一个名为 D:\BCP 的目录。A directory named D:\BCP will be used in many of the examples.

下面的脚本创建 WideWorldImporters.Warehouse.StockItemTransactions 表的空副本,然后添加主键约束。The script below creates an empty copy of the WideWorldImporters.Warehouse.StockItemTransactions table and then adds a primary key constraint. 在 SQL Server Management Studio (SSMS) 中运行以下 T-SQL 脚本Run the following T-SQL script in SQL Server Management Studio (SSMS)

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 the StockItemTransactions_bcp table as needed.

TRUNCATE TABLE WideWorldImporters.Warehouse.StockItemTransactions_bcp;TRUNCATE TABLE WideWorldImporters.Warehouse.StockItemTransactions_bcp;

A.A. 标识 bcp 实用工具版本Identify bcp utility version

在命令提示符处输入以下命令:At a command prompt, enter the following command:

bcp -v

B.B. 将表行复制到数据文件中(使用可信连接)Copying table rows into a data file (with a trusted connection)

以下示例阐释了 WideWorldImporters.Warehouse.StockItemTransactions 表中的 out 选项。The following examples illustrates the out option on the WideWorldImporters.Warehouse.StockItemTransactions table.

  • 基本Basic
    此示例创建一个名为 StockItemTransactions_character.bcp 的数据文件,并使用 字符 格式将表数据复制到该文件中。This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

    在命令提示符处输入以下命令:At a command prompt, enter the following command:

    bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\BCP\StockItemTransactions_character.bcp -c -T
    
    • 扩展Expanded
      此示例创建一个名为 StockItemTransactions_native.bcp 的数据文件,并使用 本机 格式将表数据复制到该文件中。This example creates a data file named StockItemTransactions_native.bcp and copies the table data into it using the native format. 此示例还指定最大语法错误数、一个错误文件和一个输出文件。The example also: specifies the maximum number of syntax errors, an error file, and an output file.

      在命令提示符处输入以下命令:At a command prompt, enter the following command:

      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 -T
      

查看 Error_out.logOutput_out.logReview Error_out.log and Output_out.log. Error_out.log 应为空白。Error_out.log should be blank. 比较 StockItemTransactions_character.bcpStockItemTransactions_native.bcp之间的文件大小。Compare the file sizes between StockItemTransactions_character.bcp and StockItemTransactions_native.bcp.

C.C. 将表行复制到数据文件中(使用混合模式身份验证)Copying table rows into a data file (with mixed-mode authentication)

以下示例阐释了 表中的 out WideWorldImporters.Warehouse.StockItemTransactions 选项。The following example illustrates the out option on the WideWorldImporters.Warehouse.StockItemTransactions table. 此示例创建一个名为 StockItemTransactions_character.bcp 的数据文件,并使用 字符 格式将表数据复制到该文件中。This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

该示例假定你使用混合模式身份验证,必须使用 -U 开关指定登录 ID。The example assumes that you are using mixed-mode authentication, you must use the -U switch to specify your login ID. 并且,除非你连接到本地计算机上 SQL ServerSQL Server 的默认实例,否则请使用 -S 开关指定系统名称和实例名称(可选)。Also, unless you are connecting to the default instance of SQL ServerSQL Server on the local computer, use the -S switch to specify the system name and, optionally, an instance name.

在命令提示符处,输入以下命令: (系统将提示你输入密码。)At a command prompt, enter the following command: (The system will prompt you for your password.)

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

D.D. 将文件中的数据复制到表中Copying data from a file to a table

以下示例使用上面创建的文件说明 WideWorldImporters.Warehouse.StockItemTransactions_bcp 表中的 in 选项。The following examples illustrate the in option on the WideWorldImporters.Warehouse.StockItemTransactions_bcp table using files created above.

  • 基本Basic
    此示例使用以前创建的 StockItemTransactions_character.bcp 数据文件。This example uses the StockItemTransactions_character.bcp data file previously created.

    在命令提示符处输入以下命令:At a command prompt, enter the following command:

    bcp WideWorldImporters.Warehouse.StockItemTransactions_bcp IN D:\BCP\StockItemTransactions_character.bcp -c -T  
    
  • 扩展Expanded
    此示例使用以前创建的 StockItemTransactions_native.bcp 数据文件。This example uses the StockItemTransactions_native.bcp data file previously created. 此示例还使用提示 TABLOCK,指定最大语法错误数、一个错误文件和一个输出文件。The example also: use the hint TABLOCK, specifies the batch size, the maximum number of syntax errors, an error file, and an output file.

    在命令提示符处输入以下命令:At a command prompt, enter the following command:

    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 -T 
    

    查看 Error_in.logOutput_in.logReview Error_in.log and Output_in.log.

E.E. 将特定的列复制到数据文件中Copying a specific column into a data file

若要复制特定列,可以使用 queryout 选项。To copy a specific column, you can use the queryout option. 下面的示例仅将 StockItemTransactionID 表中的 Warehouse.StockItemTransactions 列复制到数据文件中。The following example copies only the StockItemTransactionID column of the Warehouse.StockItemTransactions table into a data file.

在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

F.F. 将特定的行复制到数据文件中Copying a specific row into a data file

若要复制特定行,可以使用 queryout 选项。To copy a specific row, you can use the queryout option. 以下示例仅将名为 Amy Trefl 的人员行从 WideWorldImporters.Application.People 表复制到数据文件 Amy_Trefl_c.bcp 中。The following example copies only the row for the person named Amy Trefl from the WideWorldImporters.Application.People table into a data file Amy_Trefl_c.bcp. 注意: -d 开关用于标识数据库。Note: the -d switch is used identify the database.

在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

G.G. 将查询中的数据复制到数据文件中Copying data from a query to a data file

若要将 Transact-SQL 语句的结果集复制到数据文件中,请使用 queryout 选项。To copy the result set from a Transact-SQL statement to a data file, use the queryout option. 下面的示例将 WideWorldImporters.Application.People 表中的姓名复制到 People.txt 数据文件中;这些姓名按全名排序。The following example copies the names from the WideWorldImporters.Application.People table, ordered by full name, into the People.txt data file. 注意: -t 开关用于创建逗号分隔文件。Note: the -t switch is used to create a comma delimited file.

在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

H.H. 创建格式化文件Creating format files

下面的示例为 Warehouse.StockItemTransactions 数据库中的 WideWorldImporters 表创建三个不同格式文件。The following example creates three different format files for the Warehouse.StockItemTransactions table in the WideWorldImporters database. 查看创建的每个文件的内容。Review the contents of each created file.

在命令提示符处输入以下命令:At a command prompt, enter the following commands:

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

备注

若要使用 -x 开关,则必须使用 bcp 9.0 客户端。To use the -x switch, you must be using a bcp 9.0 client. 有关如何使用 bcp 9.0 客户端的信息,请参阅“备注”。For information about how to use the bcp 9.0 client, see "Remarks."

有关详细信息,请参阅非 XML 格式化文件 (SQL Server)XML 格式化文件 (SQL Server)For more information, see Non-XML Format Files (SQL Server) and XML Format Files (SQL Server).

I.I. 使用格式化文件进行 bcp 批量导入Using a format file to bulk import with bcp

SQL ServerSQL Server的实例中导入数据时,若要使用以前创建的格式化文件,请同时使用 -f 开关和 in 选项。To use a previously created format file when importing data into an instance of SQL ServerSQL Server, use the -f switch with the in option. 例如,以下命令通过使用以前创建的格式化文件 ( StockItemTransactions_character.bcp),将数据文件 Warehouse.StockItemTransactions_bcp 的内容大容量复制到 StockItemTransactions_c.xml表的副本中。For example, the following command bulk copies the contents of a data file, StockItemTransactions_character.bcp, into a copy of the Warehouse.StockItemTransactions_bcp table by using the previously created format file, StockItemTransactions_c.xml. 注意: -L 开关用于仅导入前 100 个记录。Note: the -L switch is used to import only the first 100 records.

在命令提示符处输入以下命令:At a command prompt, enter the following command:

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

备注

如果数据文件字段和表中的列不同(例如,在编号、排序或数据类型方面),则可使用格式化文件。Format files are useful when the data file fields are different from the table columns; for example, in their number, ordering, or data types. 有关详细信息,请参阅 用来导入或导出数据的格式化文件 (SQL Server)知识。For more information, see Format Files for Importing or Exporting Data (SQL Server).

J.J. 指定代码页Specifying a code page

以下不完整的代码示例显示了指定代码页 65001 时使用的 bcp import 语句:The following partial code example shows bcp import while specifying a code page 65001:

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

以下不完整的代码示例显示了指定代码页 65001 时使用的 bcp export 语句:The following partial code example shows bcp export while specifying a code page 65001:

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

其他示例Additional Examples

以下主题包含有关使用 bcp的示例:The following topics contain examples of using bcp:
用于批量导入或导出的数据格式 (SQL Server)Data Formats for Bulk Import or Bulk Export (SQL Server)
 ● 使用本机格式导入或导出数据 (SQL Server) ● Use Native Format to Import or Export Data (SQL Server)
 ● 使用字符格式导入或导出数据 (SQL Server) ● Use Character Format to Import or Export Data (SQL Server)
 ● 使用 Unicode 本机格式导入或导出数据 (SQL Server) ● Use Unicode Native Format to Import or Export Data (SQL Server)
 ● 使用 Unicode 字符格式导入或导出数据 (SQL Server) ● Use Unicode Character Format to Import or Export Data (SQL Server)

指定字段终止符和行终止符 (SQL Server)Specify Field and Row Terminators (SQL Server)

在批量导入期间保留 Null 或使用默认值 (SQL Server)Keep Nulls or Use Default Values During Bulk Import (SQL Server)

批量导入数据时保留标识值 (SQL Server)Keep Identity Values When Bulk Importing Data (SQL Server)

用于导入或导出数据的格式化文件 (SQL Server))Format Files for Importing or Exporting Data (SQL Server))
 ● 创建格式化文件 (SQL Server) ● Create a Format File (SQL Server)
 ● 使用格式化文件批量导入数据 (SQL Server) ● Use a Format File to Bulk Import Data (SQL Server)
 ● 使用格式化文件跳过表列 (SQL Server) ● Use a Format File to Skip a Table Column (SQL Server)
 ● 使用格式化文件跳过数据字段 (SQL Server) ● Use a Format File to Skip a Data Field (SQL Server)
 ● 使用格式化文件将表列映射到数据文件字段 (SQL Server) ● Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

批量导入和导出 XML 文档的示例 (SQL Server)Examples of Bulk Import and Export of XML Documents (SQL Server)

另请参阅See Also

准备用于大容量导出或导入 ( 的数据SQL server) Prepare Data for Bulk Export or Import (SQL Server)
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
设置 QUOTED_IDENTIFIER (Transact SQL ) SET QUOTED_IDENTIFIER (Transact-SQL)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
sp_tableoption (Transact SQL ) sp_tableoption (Transact-SQL)
用来导入或导出数据的格式化文件 (SQL Server)Format Files for Importing or Exporting Data (SQL Server)