bcp 命令详解

bcp文件

　　存储由大容量复制实用工具或同步创建的大容量复制数据的文件。
　　 语法:
　　bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
　　{in | out | queryout | format} data_file
　　[-mmax_errors] [-fformat_file] [-x] [-eerr_file]
　　[-Ffirst_row] [-Llast_row] [-bbatch_size]
　　[-n] [-c] [-w] [-N] [-V (60 | 65 | 70 | 80)] [-6]
　　[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
　　[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
　　[-Sserver_name[/instance_name]] [-Ulogin_id] [-Ppassword]
　　[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
　　 参数含义:
　　database_name
　　指定的表或视图所在数据库的名称。如果不指定，则使用用户的默认数据库。
　　owner
　　表或视图所有者的名称。如果执行该操作的用户拥有指定的表或视图，则 owner 是可选的。如果未指定 owner，并且执行该操作的用户没有指定的表或视图，则 SQL Server 2005 将返回错误消息，同时取消操作。
　　table_name
　　将数据导入 SQL Server (in) 时的目标表名称，以及将数据从 SQL Server (out) 导出时的源表名称。
　　view_name
　　将数据复制到 SQL Server (in) 时的目标视图名称，以及复制 SQL Server (out) 中的数据时的源视图名称。只有其中所有列都引用同一个表的视图才能用作目标视图。有关将数据复制到视图的限制的详细信息，请参阅 INSERT (Transact-SQL)。
　　query
　　一个返回结果集的 Transact-SQL 查询。如果该查询返回多个结果集（如包含 COMPUTE 子句的 SELECT 语句），则只将第一个结果集复制到数据文件，而忽略其它结果集。请将查询放在英文双引号中，将查询中嵌入的任何内容放在英文单引号中。从查询大容量复制数据时，还必须指定 queryout。
　　in | , out| , queryout | , format
　　指定大容量复制的方向，具体如下：
　　in 从文件复制到数据库表或视图。
　　注意：
　　SQL Server 6.5 的 bcp 实用工具不支持向包含 sql_variant 或 bigint 数据类型的表进行大容量复制。
　　out 从数据库表或视图复制到文件。
　　queryout 从查询中复制，仅当从查询大容量复制数据时才必须指定此选项。
　　format 根据指定的选项（-n、-c、-w、-6 或 -N）以及表或视图的分隔符创建格式文件。大容量复制数据时，bcp 命令可以引用一个格式文件，从而避免以交互方式重复输入格式信息。format 选项要求指定 -f 选项；创建一个 XML 格式文件时还需要指定 -x 选项。有关详细信息，请参阅创建格式化文件。
　　data_file
　　数据文件的完整路径。将数据大容量导入 SQL Server 时，数据文件将包含要复制到指定表或视图的数据。从 SQL Server 大容量导出复制数据时，数据文件将包含从表或视图复制的数据。路径可以有 1 到 255 个字符。数据文件最多可包含 2,147,483,647 行。
　　重要事项：
　　对于 format 选项，必须指定 nul 作为 data_file (format nul) 的值。
　　-m max_errors
　　指定 bcp 操作允许的语法错误最大数目。语法错误是指将数据转换为目标数据类型时的错误。max_errors 总数不包括只能在服务器中检测到的错误，如违反约束。
　　无法由 bcp 复制的每个行都将被忽略，并计为一个错误。如果不指定此选项，则默认为 10。
　　注意：
　　-m 选项也不用于转换 money 或 bigint 数据类型。
　　-f format_file
　　指定一个格式文件的完整路径。该选项的含义取决于使用它的环境，具体如下：
　　如果 -f 与 format 选项一起使用，则将为指定的表或视图创建指定的 format_file。若要创建 XML 格式文件，请同时指定 -x 选项。
　　如果与 in 或 out 一起使用，则应为 -f 指定一个现有的格式文件。
　　注意：
　　与 in 或 out 选项一起使用时，格式文件是可选的。如果没有 -f 选项，则在未指定 -n、-c、-w、-6 或 -N 时，该命令将提示输入格式信息，并允许用户将回应保存在格式文件（默认名称为 Bcp.fmt）中。
　　-x
　　与 format 和 -f format_file 选项一起使用，可以生成基于 XML 的格式化文件，而不是默认的非 XML 格式化文件。在导入或导出数据时，-x 不起作用。如果不与 format 和 -f format_file 一起使用，则将生成错误。
　　-e err_file
　　指定错误文件的完整路径，此文件用于存储 bcp 无法从文件传输到数据库的所有行。bcp 命令产生的错误消息将被发送到用户的工作站。如果不使用此选项，则不会创建错误文件。
　　-F first_row
　　指定要从表中导出或从数据文件导入的第一行的编号。此参数应大于 (>) 0，小于 (<) 或等于 (=) 总行数。如果不指定此参数，则默认为文件的第一行。
　　-L last_row
　　指定要从表中导出或从数据文件导入的最后一行的编号。此参数应大于 (>) 0，小于 (<) 或等于 (=) 最后一行的编号。如果不指定该参数，则默认为文件的最后一行。
　　-b batch_size
　　指定每批导入数据的行数。每批均作为一个单独的事务进行导入并记录，在提交之前会导入整批。默认情况下，数据文件中的所有行均作为一批导入。若要在多批之间分布行，请指定小于数据文件中行数的 batch_size。如果任何批的事务失败，则只回滚当前批中的插入。已经由已提交事务导入的批不会受到将来失败的影响。
　　请不要将该选项与 -h"ROWS_PER_BATCH = bb" 选项一起使用。
　　-n
　　使用数据的本机（数据库）数据类型执行大容量复制操作。此选项不提示输入每个字段，它将使用本机值。
　　-c
　　使用字符数据类型执行该操作。此选项不提示输入每个字段；它使用 char 作为存储类型，不带前缀；使用 /t（制表符）作为字段分隔符，使用 /n（换行符）作为行终止符。
　　-w
　　使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段；它使用 nchar 作为存储类型，不带前缀；使用 /t（制表符）作为字段分隔符，使用 /n（换行符）作为行终止符。该选项不能在 SQL Server 6.5 或更低版本中使用。
　　-N
　　执行大容量复制操作时，对非字符数据使用本机（数据库）数据类型数据，对字符数据使用 Unicode 字符。此选项是 -w 选项的一个替代选项，并具有更高的性能。该选项主要用于使用数据文件，将数据从一个 SQL Server 实例传送到另一个实例。该选项不提示输入每个字段。如果要传送包含 ANSI 扩展字符的数据，并希望利用本地模式的性能优势，则可使用此选项。-N 不能在 SQL Server 6.5 或更低版本中使用。
　　-V ( 60| 65| 70| 80)
　　使用 SQL Server 早期版本中的数据类型执行大容量复制操作。此选项并不提示输入每个字段，它将使用默认值。例如，若要将随 SQL Server 6.5 提供的 bcp 实用工具支持（但 ODBC 不再支持）的日期格式大容量复制到 SQL Server 2005，可使用 -V 65 参数。
　　重要事项：
　　如果将大量数据从 SQL Server 导出到数据文件，那么即使指定了 -V，bcp 实用工具也不会为任何 datetime 或 smalldatetime 数据生成SQL Server 6.0 或 SQL Server 6.5 日期格式。日期将始终以 ODBC 格式写入。另外，由于 SQL Server 6.5 或更低版本不支持可为空的 bit 数据，因此位列中的空值将写为值 0。
　　-6
　　使用 SQL Server 6.0 或 SQL Server 6.5 数据类型执行大容量复制操作。支持此选项仅为与较早版本兼容。对于 SQL Server 7 及更高版本，请使用 -V 选项。
　　-q
　　在连接 bcp 实用工具和 SQL Server 实例时，执行 SET QUOTED_IDENTIFIERS ON 语句。使用此选项可以指定包含空格或单引号的数据库、所有者、表或视图的名称。将由三部分组成的整个表名或视图名用英文双引号 ("") 括起来。
　　若要指定包含空格或单引号的数据库名称，必须使用 -q 选项。
　　有关详细信息，请参阅本主题后面的“备注”。
　　-C { ACP | OEM | RAW | code_page }
　　用于兼容 SQL Server 的早期版本。对于 SQL Server 7.0 及更高版本，Microsoft 建议在格式文件中为每个列指定一个排序规则名称。
　　指定数据文件中数据的代码页。仅当数据包含字符值大于 127 或小于 32 的 char、varchar 或 text 列时，code_page 才适用。
　　代码页值 说明
　　ACP
　　ANSI/Microsoft Windows (ISO 1252)。
　　OEM
　　客户端使用的默认代码页。未指定 -C 选项时使用的默认代码页。
　　RAW
　　不进行代码页间的转换。因为不进行转换，所以这是最快的选项。
　　code_page
　　特定的代码页编号，例如 850。
　　-t field_term
　　指定字段终止符。默认值为 /t（制表符）。使用此参数可以替代默认字段终止符。
　　-r row_term
　　指定行终止符。默认值为 /n（换行符）。使用此参数可替代默认行终止符。
　　-i input_file
　　指定响应文件的名称，其中包含在交互模式（未指定 -n、-c、-w、-6 或 -N）下执行大容量复制时，对该命令要求输入每个数据字段的提示信息作出的响应。
　　-o output_file
　　指定文件名称，该文件用于接收从命令提示符重定向来的输出。
　　-a packet_size
　　指定服务器发出或接收的每个网络数据包的字节数。可以使用 SQL Server Management Studio（或 sp_configure 系统存储过程）来设置服务器配置选项。但是，可以使用该选项逐个替代服务器配置选项。packet_size 的取值范围为 4096 到 65535 字节，默认为 4096 字节。
　　增大数据包可以提高大容量复制操作的性能。如果无法得到请求的较大数据包，则使用默认值。bcp 实用工具生成的性能统计信息可以显示所用数据包的大小。
　　-S server_name[ /instance_name]
　　指定要连接的 SQL Server 的实例。如果不指定服务器，则 bcp 实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机运行 bcp ，则必须使用此选项。若要连接到服务器的 SQL Server 默认实例，请仅指定 server_name。若要连接到 SQL Server 2005 的命名实例，请指定 server_name/instance_name。
　　-U login_id
　　指定用于连接 SQL Server 的登录 ID。
　　安全性 注意：
　　如果 bcp 实用工具使用集成安全性的可信连接与 SQL Server 进行连接，则使用 -T 选项（可信连接），而不要使用 user name 和 password 组合。
　　-P password
　　指定登录 ID 的密码。如果不使用此选项，bcp 命令将提示输入密码。如果在命令提示符的末尾使用此选项，但不提供密码，则 bcp 将使用默认密码 (NULL)。
　　安全性 注意：
　　不要使用空密码。请使用不易破解的密码。
　　若要屏蔽密码，请不要同时使用 -U 和 -P 选项。而应在指定 bcp 以及 -U 选项和其他开关（不指定 -P）之后，按 Enter 键，这时命令会提示输入密码。这种方法可以确保密码在输入时被屏蔽。
　　-T
　　指定 bcp 实用工具通过使用集成安全性的受信任连接连接到 SQL Server。不需要网络用户的安全凭据、login_id 和 password。如果不指定 –T，则需要指定 –U 和 –P 才能成功登录。
　　-v
　　报告 bcp 实用工具的版本号和版权。
　　-R
　　指定使用客户端计算机区域设置中定义的区域格式，将货币、日期和时间数据大容量复制到 SQL Server 中。默认情况下，将忽略区域设置。
　　-k
　　指定在操作过程中空列应该保留空值，而不是所插入列的任何默认值。
　　-E
　　指定导入的数据文件中的标识值用于标识列。如果不指定 -E，则将忽略被导入的数据文件中此列的标识值，而且 SQL Server 2005 将根据创建表期间指定的种子值和增量值自动分配唯一值。
　　假如数据文件不包含表或视图中的标识列的值，则可在格式文件指定，在导入数据时忽略表或视图中的标识列；SQL Server 2005 将自动为该列分配唯一值。有关详细信息，请参阅 DBCC CHECKIDENT (Transact-SQL)。
　　-E 选项有一个特殊的权限要求。有关详细信息，请参阅本主题后面的“备注”。
　　-h " hint[ ,... n] "
　　指定向表或视图中大容量导入数据时所用的提示。向 SQL Server 6.x 或更低版本大容量复制数据时，不能使用此选项。
　　ORDER(column [ASC | DESC] [,...n])
　　数据文件中的数据排序次序。如果根据表中的聚集索引对要加载的数据排序，则可提高大容量复制的性能。如果数据文件按不同次序排序，或者该表没有聚集索引，则将忽略 ORDER 提示。提供的列名必须是目标表中的有效列。默认情况下，bcp 假设数据文件没有排序。
　　ROWS_PER_BATCH = bb
　　每批中数据的行数（即 bb）。在未指定 -b 时使用，这将导致整个数据文件被作为单个事务发送到服务器。服务器根据 bb 值优化大容量加载。默认情况下，ROWS_PER_BATCH 未知。
　　KILOBYTES_PER_BATCH = cc
　　每批以千字节计的数据的近似值（即 cc）。默认情况下，KILOBYTES_PER_BATCH 未知。
　　TABLOCK
　　指定在大容量加载操作期间获取大容量更新表级别的锁；否则，获取行级别的锁。由于在大容量复制操作期间拥有锁可以减少表中的锁争夺，所以此提示可显著提高性能。如果表没有索引并且指定了 TABLOCK，则该表可以同时被多个客户端装载。默认情况下，锁定行为由表选项 table lock on bulk load 确定。
　　CHECK_CONSTRAINTS
　　指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。如果没有 CHECK_CONSTRAINTS 提示，则忽略所有 CHECK 约束；操作完成后，对表的约束将被标记为不可信。
　　注意：
　　始终强制使用 UNIQUE、PRIMARY KEY、FOREIGN KEY 或 NOT NULL 约束。
　　在某些时候，需要检查整个表的约束。如果在大容量导入操作之前表为非空状态，则重新验证约束的开销可能超过将 CHECK 约束应用于增量数据的开销。因此，Microsoft 建议您在正常情况下，在进行增量式大容量导入时，启用约束检查。
　　当输入数据包含违反约束的行时，您可能希望禁用约束（默认行为）。如果禁用 CHECK 约束，您可以导入数据，然后使用 Transact-SQL 语句删除无效数据。
　　注意：
　　在 SQL Server 2005 中，bcp 会强制执行新的数据验证和数据检查，这将导致对数据文件中的无效数据执行现有脚本时，可能会失败。
　　注意：
　　-mmax_errors 开关不适用于约束检查。
　　FIRE_TRIGGERS
　　与 in 参数一同指定，对目标表中定义的任何插入触发器都将在大容量复制操作期间运行。如果不指定 FIRE_TRIGGERS，将不运行任何插入触发器。对于 out、queryout 和 format 参数，将忽略 FIRE_TRIGGERS。
　　注意：
　　每个批的触发器只运行一次（而不是每个行一次）。
　　 示例
　　将表行复制到数据文件（使用可信连接）
　　下面的示例阐释了 AdventureWorks.Sales.Currency 表中的 out 选项。此示例创建一个名为 Currency.dat 的数据文件，并使用字符格式将表数据复制到该文件中。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任连接。
　　在命令提示符处输入以下命令：
　　bcp AdventureWorks.Sales.Currency out Currency.dat -T -c
　　将表行复制到数据文件（使用混合模式身份验证）
　　下面的示例阐释了 AdventureWorks.Sales.Currency 表中的 out 选项。此示例创建一个名为 Currency.dat 的数据文件，并使用字符格式将表数据复制到该文件中。
　　以上示例假定您使用的是混合模式身份验证，您必须使用 -U 开关来指定登录 ID。此外，除非您连接的是本地计算中 SQL Server 上的默认实例，否则要使用 -S 开关来指定系统名称和实例名称（实例名称可选）。
　　bcp AdventureWorks.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name/instance_name>
　　系统将提示您输入密码。
　　将数据从文件复制到表。
　　以下示例使用上例创建的文件 (Currency.dat) 来阐释 in 选项。但是，此示例将首先创建一个 AdventureWorks Sales.Currency 表的空副本 Sales.Currency2，数据将被复制到该副本。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任连接。
　　若要创建空表，可在查询编辑器中输入以下命令：
　　USE AdventureWorks;
　　GO
　　SELECT * INTO AdventureWorks.Sales.Currency2
　　FROM AdventureWorks.Sales.Currency WHERE 1=2
　　若要将字符数据大容量复制到新表（即导入数据），可在命令提示符处输入以下命令：
　　bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -c
　　若要查看命令是否成功，并在查询编辑器中显示表的内容，请输入：
　　USE AdventureWorks;
　　GO
　　SELECT * FROM Sales.Currency2
　　将特定的列复制到数据文件中
　　若要复制特定列，可以使用 queryout 选项。以下示例仅将 Sales.Currency 表中的 Name 列复制到数据文件中。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任的连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c
　　将特定的行复制到数据文件中
　　若要复制特定行，可以使用 queryout 选项。以下示例仅将名为 Jarrod Rana 的联系人行从 AdventureWorks.Person.Contact 表复制到数据文件 (Jarrod Rana.dat) 中。该示例假定您使用的是 Windows 身份验证，并且与运行 bcp 命令的服务器上的实例有可信连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp "SELECT * FROM AdventureWorks.Person.Contact WHERE FirstName='Jarrod' AND LastName='Rana' " queryout "Jarrod Rana.dat" -T -c
　　将数据从查询复制到数据文件
　　若要将 Transact-SQL 语句的结果集复制到数据文件，可使用 queryout 选项。以下示例将 AdventureWorks.Person.Contact 表中的姓名复制到 Contacts.txt 数据文件中；这些姓名先按名排序，再按姓排序。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任的连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
　　创建非 XML 格式文件
　　AdventureWorks 数据库中的 Sales.Currency 表的非 XML 格式文件 Currency.fmt。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任的连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp AdventureWorks.Sales.Currency format nul -T -c -f Currency.fmt
　　有关详细信息，请参阅了解非 XML 格式化文件。
　　创建 XML 格式文件
　　以下示例为 AdventureWorks 数据库中的 Sales.Currency 表创建一个名为 Currency.xml 的 XML 格式文件。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任的连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp AdventureWorks.Sales.Currency format nul -T -c -x -f Currency.xml
　　有关详细信息，请参阅了解 XML 格式化文件。
　　使用格式文件进行 bcp 大容量导入
　　向 SQL Server 的实例导入数据时，若要使用以前创建的格式文件，可同时使用 -f 开关和 in 选项。例如，以下命令通过使用先前创建的格式文件（Currency.xml），将数据文件 Currency.dat 的内容大容量复制到 Sales.Currency 表 (Sales.Currency2) 的副本中。此示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有受信任的连接。
　　在 Windows 命令提示符下，输入以下内容：
　　bcp AdventureWorks.Sales.Currency2 in Currency.dat -T -f Currency.xml
　　如果数据文件字段和表中的列不同（例如，在编号、排序或数据类型方面），则可使用格式文件。