BCP命令-SQLserver数据快速导入导出

《BCP命令-SQLserver数据快速导入导出》由会员分享，可在线阅读，更多相关《BCP命令-SQLserver数据快速导入导出（10页珍藏版）》请在装配图网上搜索。

1、文档供参考，可复制、编制，期待您的好评与关注！ bcp命令导入导出数据1 简介bcp实用工具可以在 Microsoft SQL Server实例和用户指定格式的数据文件间大容量复制数据。使用bcp实用工具可以将大量新行导入 SQL Server 表，或将表数据导入数据文件。除非与queryout选项一起使用，否则使用该实用工具不需要了解 Transact-SQL 知识。若要将数据导入表中，必须使用为该表创建的格式文件，或者必须了解表的结构以及对于该表中的列有效的数据类型。2 语法bcp database_name.owner.table_name | view_name | queryin |

2、 out | queryout | format data_file-mmax_errors -fformat_file -x -eerr_file-Ffirst_row -Llast_row -bbatch_size-n -c -N -w -V (60 | 65 | 70 | 80) -6 -q -C ACP | OEM | RAW | code_page -tfield_term -rrow_term -iinput_file -ooutput_file -apacket_size-Sserver_nameinstance_name -Ulogin_id -Ppassword-T -v -

3、R -k -E -hhint ,.n3 参数说明database_name指定的表或视图所在数据库的名称。如果不指定，则使用用户的默认数据库。owner表或视图所有者的名称。如果执行该操作的用户拥有指定的表或视图，则owner是可选的。如果未指定owner，并且执行该操作的用户没有指定的表或视图，则 SQL Server 2005 将返回错误消息，同时取消操作。table_name将数据导入 SQL Server (in) 时的目标表名称，以及将数据从 SQL Server (out) 导出时的源表名称。view_name将数据复制到 SQL Server (in) 时的目标视图名称，以及复制

4、 SQL Server (out) 中的数据时的源视图名称。只有其中所有列都引用同一个表的视图才能用作目标视图。有关将数据复制到视图的限制的详细信息，请参阅INSERT (Transact-SQL)。query一个返回结果集的 Transact-SQL 查询。如果该查询返回多个结果集（如包含 COMPUTE 子句的 SELECT 语句），则只将第一个结果集复制到数据文件，而忽略其它结果集。请将查询放在英文双引号中，将查询中嵌入的任何内容放在英文单引号中。从查询大容量复制数据时，还必须指定queryout。in|out|queryout|format指定大容量复制的方向，具体如下： in从文件复

5、制到数据库表或视图。注意：SQL Server 6.5 的bcp实用工具不支持向包含sql_variant或bigint数据类型的表进行大容量复制。 out从数据库表或视图复制到文件。提取数据时，请注意bcp实用工具将空字符串表示为 null，而将 null 字符串表示为空字符串。 queryout从查询中复制，仅当从查询大容量复制数据时才必须指定此选项。 format根据指定的选项（-n、-c、-w、-6或-N）以及表或视图的分隔符创建格式文件。大容量复制数据时，bcp命令可以引用一个格式文件，从而避免以交互方式重复输入格式信息。format选项要求指定-f选项；创建一个 XML 格式文件时

6、还需要指定-x选项。有关详细信息，请参阅创建格式化文件。data_file数据文件的完整路径。将数据大容量导入 SQL Server 时，数据文件将包含要复制到指定表或视图的数据。从 SQL Server 大容量导出复制数据时，数据文件将包含从表或视图复制的数据。路径可以有 1 到 255 个字符。数据文件最多可包含 2,147,483,647 行。重要提示：对于format选项，必须指定nul作为data_file (formatnul) 的值。 -mmax_errors指定取消bcp操作之前可能出现的语法错误的最大数目。语法错误是指将数据转换为目标数据类型时的错误。max_errors总数

7、不包括只能在服务器中检测到的错误，如违反约束。无法由bcp实用工具复制的行将被忽略，并计为一个错误。如果不指定此选项，则默认为 10。注意：-m选项也不用于转换money或bigint数据类型。 -fformat_file指定一个格式文件的完整路径。该选项的含义取决于使用它的环境，具体如下： 如果-f与format选项一起使用，则将为指定的表或视图创建指定的format_file。若要创建 XML 格式文件，请同时指定-x选项。有关详细信息，请参阅创建格式化文件。 如果与in或out一起使用，则应为-f指定一个现有的格式文件。注意：与in或out选项一起使用时，格式文件是可选的。如果没有-f选

8、项，则在未指定-n、-c、-w、-6或-N时，该命令将提示输入格式信息，并允许用户将回应保存在格式文件（默认名称为 Bcp.fmt）中。 -x与format和-fformat_file选项一起使用，可以生成基于 XML 的格式化文件，而不是默认的非 XML 格式化文件。在导入或导出数据时，-x不起作用。如果不与format和-fformat_file一起使用，则将生成错误。注意：若要使用-x开关，则必须使用bcp9.0 客户端。有关如何使用bcp9.0 客户端的信息，请参阅本主题后面的“备注”。 -eerr_file指定错误文件的完整路径，此文件用于存储bcp无法从文件传输到数据库的所有行。b

9、cp命令产生的错误消息将被发送到用户的工作站。如果不使用此选项，则不会创建错误文件。-Ffirst_row指定要从表中导出或从数据文件导入的第一行的编号。此参数应大于 () 0，小于 () 0，小于 () 或等于 (=) 最后一行的编号。如果不指定该参数，则默认为文件的最后一行。-bbatch_size指定每批导入数据的行数。每批均作为一个单独的事务进行导入并记录，在提交之前会导入整批。默认情况下，数据文件中的所有行均作为一批导入。若要在多批之间分布行，请指定小于数据文件中行数的batch_size。如果任何批的事务失败，则只回滚当前批中的插入。已经由已提交事务导入的批不会受到将来失败的影响。

10、请不要将该选项与hROWS_PER_BATCH=bb选项一起使用。有关详细信息，请参阅管理大容量导入的批处理。-n使用数据的本机（数据库）数据类型执行大容量复制操作。此选项不提示输入每个字段，它将使用本机值。有关详细信息，请参阅使用本机格式导入或导出数据。-c使用字符数据类型执行该操作。此选项不提示输入每个字段；它使用char作为存储类型，不带前缀；使用t（制表符）作为字段分隔符，使用rn（换行符）作为行终止符。有关详细信息，请参阅使用字符格式导入或导出数据。-N执行大容量复制操作时，对非字符数据使用本机（数据库）数据类型数据，对字符数据使用 Unicode 字符。此选项是-w选项的一个替代选

11、项，并具有更高的性能。该选项主要用于使用数据文件，将数据从一个 SQL Server 实例传送到另一个实例。该选项不提示输入每个字段。如果要传送包含 ANSI 扩展字符的数据，并希望利用本机模式的性能优势，则可使用此选项。-N不能在 SQL Server 6.5 或更低版本中使用。有关详细信息，请参阅使用 Unicode 本机格式导入或导出数据。-w使用 Unicode 字符执行大容量复制操作。此选项不提示输入每个字段；它使用nchar作为存储类型，不带前缀；使用t（制表符）作为字段分隔符，使用n（换行符）作为行终止符。该选项不能在 SQL Server 6.5 或更低版本中使用。有关详细信息

12、，请参阅使用 Unicode 字符格式导入或导出数据。-V(60|65|70|80)使用 SQL Server 早期版本中的数据类型执行大容量复制操作。此选项并不提示输入每个字段，它将使用默认值。例如，若要将随 SQL Server 6.5 提供的bcp实用工具支持（但 ODBC 不再支持）的日期格式大容量复制到 SQL Server 2005，可使用-V65参数。重要提示：如果将大量数据从 SQL Server 导出到数据文件，那么即使指定了-V，bcp实用工具也不会为任何datetime或smalldatetime数据生成SQL Server 6.0 或 SQL Server 6.5 日期

13、格式。日期将始终以 ODBC 格式写入。另外，由于 SQL Server 6.5 或更低版本不支持可为空的bit数据，因此位列中的空值将写为值 0。 有关详细信息，请参阅导入来自早期版本的 SQL Server 的本机格式数据和字符格式数据。-6使用 SQL Server 6.0 或 SQL Server 6.5 数据类型执行大容量复制操作。支持此选项仅为与较早版本兼容。对于 SQL Server 7 及更高版本，请使用-V选项。-q在连接bcp实用工具和 SQL Server 实例时，执行 SET QUOTED_IDENTIFIERS ON 语句。使用此选项可以指定包含空格或单引号的数据库、

14、所有者、表或视图的名称。将由三部分组成的整个表名或视图名用英文双引号 () 括起来。若要指定包含空格或单引号的数据库名称，必须使用-q选项。有关详细信息，请参阅本主题后面的“备注”。-CACP|OEM|RAW|code_page用于兼容 SQL Server 的早期版本。对于 SQL Server 7.0 及更高版本，Microsoft 建议在格式化文件中为每个列指定一个排序规则名称。指定数据文件中数据的代码页。仅当数据包含字符值大于 127 或小于 32 的char、varchar或text列时，code_page才适用。代码页值说明ACPANSI/MicrosoftWindows (ISO

15、 1252)。OEM客户端使用的默认代码页。未指定-C选项时使用的默认代码页。RAW不进行代码页间的转换。因为不进行转换，所以这是最快的选项。code_page特定的代码页编号，例如 850。有关详细信息，请参阅在不同排序规则间复制数据。-tfield_term指定字段终止符。默认值为t（制表符）。使用此参数可以替代默认字段终止符。有关详细信息，请参阅指定字段终止符和行终止符。-rrow_term指定行终止符。默认值为n（换行符）。使用此参数可替代默认行终止符。有关详细信息，请参阅指定字段终止符和行终止符。-iinput_file指定响应文件的名称，其中包含在交互模式（未指定-n、-c、-w、

16、-6或-N）下执行大容量复制时，对该命令要求输入每个数据字段的提示信息作出的响应。-ooutput_file指定文件名称，该文件用于接收从命令提示符重定向来的输出。-apacket_size指定服务器发出或接收的每个网络数据包的字节数。可以使用 SQL Server Management Studio（或sp_configure系统存储过程）来设置服务器配置选项。但是，可以使用该选项逐个替代服务器配置选项。packet_size的取值范围为 4096 到 65535 字节，默认为 4096 字节。增大数据包可以提高大容量复制操作的性能。如果无法得到请求的较大数据包，则使用默认值。bcp实用工具

17、生成的性能统计信息可以显示所用数据包的大小。-Sserver_nameinstance_name指定要连接的 SQL Server 的实例。如果不指定服务器，则bcp实用工具将连接到本地计算机上的默认 SQL Server 实例。如果从网络或本地命名实例上的远程计算机运行bcp命令，则必须使用此选项。若要连接到服务器的 SQL Server 默认实例，请仅指定server_name。若要连接到 SQL Server 2005 的命名实例，请指定server_nameinstance_name。-Ulogin_id指定用于连接 SQL Server 的登录 ID。安全说明：如果bcp实用工具使用

18、集成安全性的可信连接与 SQL Server 进行连接，则使用-T选项（可信连接），而不要使用user name和password组合。 -Ppassword指定登录 ID 的密码。如果不使用此选项，bcp命令将提示输入密码。如果在命令提示符的末尾使用此选项，但不提供密码，则bcp将使用默认密码 (NULL)。安全说明：不要使用空密码。请使用强密码。 若要屏蔽密码，请不要同时使用-U和-P选项。而应在指定bcp以及-U选项和其他开关（不指定-P）之后，按 Enter 键，这时命令会提示输入密码。这种方法可以确保密码在输入时被屏蔽。-T指定bcp实用工具通过使用集成安全性的可信连接连接到 SQL

19、 Server。不需要网络用户的安全凭据、login_id和password。如果不指定T，则需要指定U和P才能成功登录。-v报告bcp实用工具的版本号和版权。-R指定使用客户端计算机区域设置中定义的区域格式，将货币、日期和时间数据大容量复制到 SQL Server 中。默认情况下，将忽略区域设置。-k指定在操作过程中空列应该保留空值，而不是所插入列的任何默认值。有关详细信息，请参阅在大容量导入期间保留空值或使用默认值。-E指定导入数据文件中的标识值用于标识列。如果不指定-E，则将忽略被导入的数据文件中此列的标识值，而且 SQL Server 2005 将根据创建表期间指定的种子值和增量值自动

20、分配唯一值。假如数据文件不包含表或视图中的标识列的值，则可在格式文件指定，在导入数据时忽略表或视图中的标识列；SQL Server 2005 将自动为该列分配唯一值。有关详细信息，请参阅DBCC CHECKIDENT (Transact-SQL)。-E选项有一个特殊的权限要求。有关详细信息，请参阅本主题后面的“备注”。有关保留标识值的详细信息，请参阅大容量导入数据时保留标识值。-hhint,.n指定向表或视图中大容量导入数据时所用的提示。向 SQL Server 6.x或更低版本大容量复制数据时，不能使用此选项。ORDER(columnASC | DESC ,.n)数据文件中的数据排序次序。如

21、果根据表中的聚集索引（如果有的话）对要导入的数据排序，则可提高大容量导入的性能。如果数据文件以不同的次序（即不同于聚集索引键的次序）排序，或者表中不存在任何聚集索引，则忽略 ORDER 子句。提供的列名必须是目标表中有效的列名。默认情况下，bcp假设数据文件没有排序。对于优化大容量导入，SQL Server 还验证导入的数据是否已经排序。有关详细信息，请参阅大容量导入数据时控制排序顺序。ROWS_PER_BATCH=bb每批中数据的行数（即bb）。在未指定-b时使用，这将导致整个数据文件被作为单个事务发送到服务器。服务器根据bb值优化大容量加载。默认情况下，ROWS_PER_BATCH 未知。

22、有关详细信息，请参阅管理大容量导入的批处理。KILOBYTES_PER_BATCH=cc每批以千字节计的数据的近似值（即cc）。默认情况下，KILOBYTES_PER_BATCH 未知。有关详细信息，请参阅管理大容量导入的批处理。TABLOCK指定在大容量加载操作期间获取大容量更新表级别的锁；否则，获取行级别的锁。由于在大容量复制操作期间拥有锁可以减少表中的锁争夺，所以此提示可显著提高性能。如果表没有索引并且指定了TABLOCK，则该表可以同时由多个客户端加载。默认情况下，锁定行为由表选项table lock on bulk load确定。有关详细信息，请参阅控制大容量导入的锁定行为。CHEC

23、K_CONSTRAINTS指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。如果没有 CHECK_CONSTRAINTS 提示，则忽略所有 CHECK 和 FOREIGN KEY 约束；操作完成后，对表的约束将被标记为不可信。注意：始终强制使用 UNIQUE、PRIMARY KEY 和 NOT NULL 约束。 在某些时候，需要检查整个表的约束。如果在大容量导入操作之前表为非空状态，则重新验证约束的开销可能超过将 CHECK 约束应用于增量数据的开销。因此，建议您在正常情况下，在进行增量式大容量导入时，启用约束检查。当输入数据包含违反约束的行时，您可能希望禁用约束（默认行为）。如果禁

24、用 CHECK 约束，您可以导入数据，然后使用 Transact-SQL 语句删除无效数据。注意：在 SQL Server 2005 中，bcp会强制执行新的数据验证和数据检查，这将导致对数据文件中的无效数据执行现有脚本时，可能会失败。 注意：-mmax_errors开关不适用于约束检查。 有关详细信息，请参阅通过大容量导入操作控制约束检查。FIRE_TRIGGERS与in参数一同指定，对目标表中定义的任何插入触发器都将在大容量复制操作期间运行。如果不指定 FIRE_TRIGGERS，将不运行任何插入触发器。对于out、queryout和format参数，将忽略 FIRE_TRIGGERS。有

25、关详细信息，请参阅导入大容量数据时控制触发器执行。4 实例1、 使用bcp命令前首先确认本地已经安装bcp工具；确认方法：打开开始菜单，在运行命令中输出“cmd”，点击“确定”， 进入cmd命令框，在cmd命令框中输入“bcp -v”(注意大小写)，如果出现类似下面字样，则说明本地已经安装bcp工具，否则请先安装SQL客户端；2、 实例1) 数据导出：bcp FangText.Fang.SmsInfo out D:SmsInfo.dat -U fang -P fang -S 192.168.1.205 c说明：此行命令表示将服务器192.168.1.205上面的FangText的数据库下面Sm

26、sInfo的表里的数据导出到本地D盘的目录下，以文件名为SmsInfo.dat存放；“bcp” 表示使用bcp命令；“FangText.Fang.SmsInfo”， 其中“FangText”表示数据库名， Fang表示对象名，“SmsInfo”表示表名；“out” 表示数据导出；“D:SmsInfo.dat” 表示将数据导入到D盘目录下，文件名为SmsInfo.dat；“-U fang”， 其中 “-U”表示用户名的命令，“fang” 表示登陆数据库用户名；“-P fang”， 其中 “-P”表示密码的命令，“fang” 表示登陆数据库该用户名对应的密码；“-S 192.168.1.205”表

27、示数据库所在的服务器名“c” 表示使用char作为存储类型，不带前缀；使用t（制表符）作为字段分隔符，使用rn（换行符）作为行终止符。2) 数据导入：bcp FangText.Fang.SmsInfo1 in D:SmsInfo.dat -U fang -P fang -S 192.168.1.205 c说明：此行命令表示将本地D盘目录下的SmsInfo.dat文件中的数据导入到服务器192.168.1.205的FangText数据库下的SmsInfo1表中；5 导入导出性能测试环境：1. 服务端配置：CPU Inter Pentium 双核2.6GHZ，内存4G，操作系统 windows s

28、erver 2003 86x32， 数据库 SQL Server 2000;2. 本地配置；CPU AMD Sempron Processor 3200+1.79GHZ，内存1G，操作系统 windows XP sp3 86x323. 测试表中的列数为40，数据122953行，单列唯一索引；导出性能：耗时4.125S， 平均值：29806.79行/秒；导入性能：耗时7.078S， 平均值：17371.15行/秒；6 注意事项当导入数据时，目标表中有单列唯一索引时，若导入的数据与表中的数据发生索引冲突时，会导致整个导入事件失败；即不会改变目标表中的原有数据；处理方法： 先用bcp把文件插入到数据库中一个临时表中，不要插入到目地表,然后再从这个临时表中把数据插入到目的表，（这个地方处理重复的数据)insert into 目的表select 主键. from 临时表 where 主键 not in(select 主键 from 目的表)10 / 10