BCP命令SQL server数据快速导入导出.docx

BCP命令SQL server数据快速导入导出.docx

《BCP命令SQL server数据快速导入导出.docx》由会员分享，可在线阅读，更多相关《BCP命令SQL server数据快速导入导出.docx（14页珍藏版）》请在冰豆网上搜索。

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

bcp命令导入导出数据

1简介

bcp 实用工具可以在MicrosoftSQLServer实例和用户指定格式的数据文件间大容量复制数据。

使用 bcp 实用工具可以将大量新行导入SQLServer表，或将表数据导入数据文件。

除非与 queryout 选项一起使用，否则使用该实用工具不需要了解Transact-SQL知识。

若要将数据导入表中，必须使用为该表创建的格式文件，或者必须了解表的结构以及对于该表中的列有效的数据类型。

2语法

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][-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_name[\instance_name]][-Ulogin_id][-Ppassword]

[-T][-v][-R][-k][-E][-h"hint[,...n]"]

3参数说明

database_name

指定的表或视图所在数据库的名称。

如果不指定，则使用用户的默认数据库。

owner

表或视图所有者的名称。

如果执行该操作的用户拥有指定的表或视图，则 owner 是可选的。

如果未指定 owner，并且执行该操作的用户没有指定的表或视图，则SQLServer2005将返回错误消息，同时取消操作。

table_name

将数据导入SQLServer（in）时的目标表名称，以及将数据从SQLServer（out）导出时的源表名称。

view_name

将数据复制到SQLServer（in）时的目标视图名称，以及复制SQLServer（out）中的数据时的源视图名称。

只有其中所有列都引用同一个表的视图才能用作目标视图。

有关将数据复制到视图的限制的详细信息，请参阅 INSERT（Transact-SQL）。

" query "

一个返回结果集的Transact-SQL查询。

如果该查询返回多个结果集（如包含COMPUTE子句的SELECT语句），则只将第一个结果集复制到数据文件，而忽略其它结果集。

请将查询放在英文双引号中，将查询中嵌入的任何内容放在英文单引号中。

从查询大容量复制数据时，还必须指定 queryout。

in | out | queryout | format

指定大容量复制的方向，具体如下：

∙in 从文件复制到数据库表或视图。

注意：

SQLServer6.5的 bcp 实用工具不支持向包含 sql_variant 或 bigint 数据类型的表进行大容量复制。

∙out 从数据库表或视图复制到文件。

提取数据时，请注意 bcp 实用工具将空字符串表示为null，而将null字符串表示为空字符串。

∙queryout 从查询中复制，仅当从查询大容量复制数据时才必须指定此选项。

∙format 根据指定的选项（-n、-c、-w、-6 或 -N）以及表或视图的分隔符创建格式文件。

大容量复制数据时，bcp 命令可以引用一个格式文件，从而避免以交互方式重复输入格式信息。

format 选项要求指定 -f 选项；创建一个XML格式文件时还需要指定 -x 选项。

有关详细信息，请参阅创建格式化文件。

data_file

数据文件的完整路径。

将数据大容量导入SQLServer时，数据文件将包含要复制到指定表或视图的数据。

从SQLServer大容量导出复制数据时，数据文件将包含从表或视图复制的数据。

路径可以有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 一起使用，则将生成错误。

注意：

若要使用 -x 开关，则必须使用 bcp 9.0客户端。

有关如何使用 bcp 9.0客户端的信息，请参阅本主题后面的“备注”。

-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（制表符）作为字段分隔符，使用 \r\n（换行符）作为行终止符。

有关详细信息，请参阅使用字符格式导入或导出数据。

-N

执行大容量复制操作时，对非字符数据使用本机（数据库）数据类型数据，对字符数据使用Unicode字符。

此选项是 -w 选项的一个替代选项，并具有更高的性能。

该选项主要用于使用数据文件，将数据从一个SQLServer实例传送到另一个实例。

该选项不提示输入每个字段。

如果要传送包含ANSI扩展字符的数据，并希望利用本机模式的性能优势，则可使用此选项。

-N 不能在SQLServer6.5或更低版本中使用。

有关详细信息，请参阅使用Unicode本机格式导入或导出数据。

-w

使用Unicode字符执行大容量复制操作。

此选项不提示输入每个字段；它使用 nchar 作为存储类型，不带前缀；使用 \t（制表符）作为字段分隔符，使用\n（换行符）作为行终止符。

该选项不能在SQLServer6.5或更低版本中使用。

有关详细信息，请参阅使用Unicode字符格式导入或导出数据。

-V （ 60| 65| 70| 80）

使用SQLServer早期版本中的数据类型执行大容量复制操作。

此选项并不提示输入每个字段，它将使用默认值。

例如，若要将随SQLServer6.5提供的bcp 实用工具支持（但ODBC不再支持）的日期格式大容量复制到SQLServer2005，可使用 -V 65 参数。

重要提示：

如果将大量数据从SQLServer导出到数据文件，那么即使指定了 -V，bcp 实用工具也不会为任何 datetime 或 smalldatetime 数据生成SQLServer6.0或SQLServer6.5日期格式。

日期将始终以ODBC格式写入。

另外，由于SQLServer6.5或更低版本不支持可为空的 bit 数据，因此位列中的空值将写为值0。

有关详细信息，请参阅导入来自早期版本的SQLServer的本机格式数据和字符格式数据。

-6

使用SQLServer6.0或SQLServer6.5数据类型执行大容量复制操作。

支持此选项仅为与较早版本兼容。

对于SQLServer7及更高版本，请使用 -V选项。

-q

在连接 bcp 实用工具和SQLServer实例时，执行SETQUOTED_IDENTIFIERSON语句。

使用此选项可以指定包含空格或单引号的数据库、所有者、表或视图的名称。

将由三部分组成的整个表名或视图名用英文双引号（""）括起来。

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

有关详细信息，请参阅本主题后面的“备注”。

-C { ACP | OEM | RAW | code_page }

用于兼容SQLServer的早期版本。

对于SQLServer7.0及更高版本，Microsoft建议在格式化文件中为每个列指定一个排序规则名称。

指定数据文件中数据的代码页。

仅当数据包含字符值大于127或小于32的 char、varchar 或 text 列时，code_page 才适用。

代码页值

说明

ACP

ANSI/MicrosoftWindows（ISO1252）。

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

指定服务器发出或接收的每个网络数据包的字节数。

可以使用SQLServerManagementStudio（或 sp_configure 系统存储过程）来设置服务器配置选项。

但是，可以使用该选项逐个替代服务器配置选项。

packet_size 的取值范围为4096到65535字节，默认为4096字节。

增大数据包可以提高大容量复制操作的性能。

如果无法得到请求的较大数据包，则使用默认值。

bcp 实用工具生成的性能统计信息可以显示所用数据包的大小。

-S server_name[ \instance_name]

指定要连接的SQLServer的实例。

如果不指定服务器，则 bcp 实用工具将连接到本地计算机上的默认SQLServer实例。

如果从网络或本地命名实例上的远程计算机运行 bcp 命令，则必须使用此选项。

若要连接到服务器的SQLServer默认实例，请仅指定 server_name。

若要连接到SQLServer2005的命名实例，请指定 server_name\instance_name。

-U login_id

指定用于连接SQLServer的登录ID。

安全说明：

如果 bcp 实用工具使用集成安全性的可信连接与SQLServer进行连接，则使用 -T 选项（可信连接），而不要使用 username 和 password 组合。

-P password

指定登录ID的密码。

如果不使用此选项，bcp 命令将提示输入密码。

如果在命令提示符的末尾使用此选项，但不提供密码，则 bcp 将使用默认密码（NULL）。

安全说明：

不要使用空密码。

请使用强密码。

若要屏蔽密码，请不要同时使用 -U 和 -P 选项。

而应在指定 bcp 以及 -U 选项和其他开关（不指定 -P）之后，按Enter键，这时命令会提示输入密码。

这种方法可以确保密码在输入时被屏蔽。

-T

指定 bcp 实用工具通过使用集成安全性的可信连接连接到SQLServer。

不需要网络用户的安全凭据、login_id 和 password。

如果不指定 –T，则需要指定 –U 和 –P 才能成功登录。

-v

报告 bcp 实用工具的版本号和版权。

-R

指定使用客户端计算机区域设置中定义的区域格式，将货币、日期和时间数据大容量复制到SQLServer中。

默认情况下，将忽略区域设置。

-k

指定在操作过程中空列应该保留空值，而不是所插入列的任何默认值。

有关详细信息，请参阅在大容量导入期间保留空值或使用默认值。

-E

指定导入数据文件中的标识值用于标识列。

如果不指定 -E，则将忽略被导入的数据文件中此列的标识值，而且SQLServer2005将根据创建表期间指定的种子值和增量值自动分配唯一值。

假如数据文件不包含表或视图中的标识列的值，则可在格式文件指定，在导入数据时忽略表或视图中的标识列；SQLServer2005将自动为该列分配唯一值。

有关详细信息，请参阅 DBCCCHECKIDENT（Transact-SQL）。

-E 选项有一个特殊的权限要求。

有关详细信息，请参阅本主题后面的“备注”。

有关保留标识值的详细信息，请参阅大容量导入数据时保留标识值。

-h " hint[ ,... n] "

指定向表或视图中大容量导入数据时所用的提示。

向SQLServer6.x 或更低版本大容量复制数据时，不能使用此选项。

ORDER（column [ASC|DESC][,...n]）

数据文件中的数据排序次序。

如果根据表中的聚集索引（如果有的话）对要导入的数据排序，则可提高大容量导入的性能。

如果数据文件以不同的次序（即不同于聚集索引键的次序）排序，或者表中不存在任何聚集索引，则忽略ORDER子句。

提供的列名必须是目标表中有效的列名。

默认情况下，bcp 假设数据文件没有排序。

对于优化大容量导入，SQLServer还验证导入的数据是否已经排序。

有关详细信息，请参阅大容量导入数据时控制排序顺序。

ROWS_PER_BATCH = bb

每批中数据的行数（即 bb）。

在未指定 -b 时使用，这将导致整个数据文件被作为单个事务发送到服务器。

服务器根据 bb 值优化大容量加载。

默认情况下，ROWS_PER_BATCH未知。

有关详细信息，请参阅管理大容量导入的批处理。

KILOBYTES_PER_BATCH = cc

每批以千字节计的数据的近似值（即 cc）。

默认情况下，KILOBYTES_PER_BATCH未知。

有关详细信息，请参阅管理大容量导入的批处理。

TABLOCK

指定在大容量加载操作期间获取大容量更新表级别的锁；否则，获取行级别的锁。

由于在大容量复制操作期间拥有锁可以减少表中的锁争夺，所以此提示可显著提高性能。

如果表没有索引并且指定了 TABLOCK，则该表可以同时由多个客户端加载。

默认情况下，锁定行为由表选项 tablelockonbulkload 确定。

有关详细信息，请参阅控制大容量导入的锁定行为。

CHECK_CONSTRAINTS

指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。

如果没有CHECK_CONSTRAINTS提示，则忽略所有CHECK和FOREIGNKEY约束；操作完成后，对表的约束将被标记为不可信。

注意：

始终强制使用UNIQUE、PRIMARYKEY和NOTNULL约束。

在某些时候，需要检查整个表的约束。

如果在大容量导入操作之前表为非空状态，则重新验证约束的开销可能超过将CHECK约束应用于增量数据的开销。

因此，建议您在正常情况下，在进行增量式大容量导入时，启用约束检查。

当输入数据包含违反约束的行时，您可能希望禁用约束（默认行为）。

如果禁用CHECK约束，您可以导入数据，然后使用Transact-SQL语句删除无效数据。

注意：

在SQLServer2005中，bcp 会强制执行新的数据验证和数据检查，这将导致对数据文件中的无效数据执行现有脚本时，可能会失败。

注意：

-mmax_errors 开关不适用于约束检查。

有关详细信息，请参阅通过大容量导入操作控制约束检查。

FIRE_TRIGGERS

与 in 参数一同指定，对目标表中定义的任何插入触发器都将在大容量复制操作期间运行。

如果不指定FIRE_TRIGGERS，将不运行任何插入触发器。

对于 out、queryout 和 format 参数，将忽略FIRE_TRIGGERS。

有关详细信息，请参阅导入大容量数据时控制触发器执行。

4实例

1、使用bcp命令前首先确认本地已经安装bcp工具；

确认方法：

打开开始菜单，在运行命令中输出“cmd”，点击“确定”，进入cmd命令框，在cmd命令框中输入“bcp-v”（注意大小写），如果出现类似下面字样，则说明本地已经安装bcp工具，否则请先安装SQL客户端；

2、实例

1）数据导出：

bcpFangText.Fang.SmsInfooutD:

\SmsInfo.dat-Ufang-Pfang-S192.168.1.205–c

说明：

此行命令表示将服务器192.168.1.205上面的FangText的数据库下面SmsInfo的表里的数据导出到本地D盘的目录下，以文件名为SmsInfo.dat存放；

“bcp”表示使用bcp命令；

“FangText.Fang.SmsInfo”，其中“FangText”表示数据库名，Fang表示对象名，“SmsInfo”表示表名；

“out”表示数据导出；

“D:

\SmsInfo.dat”表示将数据导入到D盘目录下，文件名为SmsInfo.dat；

“-Ufang”，其中“-U”表示用户名的命令，“fang”表示登陆数据库用户名；

“-Pfang”，其中“-P”表示密码的命令，“fang”表示登陆数据库该用户名对应的密码；

“-S192.168.1.205”表示数据库所在的服务器名

“–c”表示使用 char 作为存储类型，不带前缀；使用 \t（制表符）作为字段分隔符，使用 \r\n（换行符）作为行终止符。

2）数据导入：

bcpFangText.Fang.SmsInfo1inD:

\SmsInfo.dat-Ufang-Pfang-S192.168.1.205–c

说明：

此行命令表示将本地D盘目录下的SmsInfo.dat文件中的数据导入到服务器192.168.1.205的FangText数据库下的SmsInfo1表中；

5导入导出性能

测试环境：

1.服务端配置：

CPUInterPentium双核2.6GHZ，内存4G，操作系统windowsserver200386x32，数据库SQLServer2000;

2.本地配置；CPUAMDSempronProcessor3200+1.79GHZ，内存1G，操作系统windowsXPsp386x32

3.测试表中的列数为40，数据122953行，单列唯一索引；

导出性能：

耗时4.125S，平均值：

29806.79行/秒；

导入性能：

耗时7.078S，平均值：

17371.15行/秒；

6注意事项

当导入数据时，目标表中有单列唯一索引时，若导入的数据与表中的数据发生索引冲突时，会导致整个导入事件失败；即不会改变目标表中的原有数据；

处理方法：

 先用bcp把文件插入到数据库中一个临时表中，不要插入到目地表,然后再从这个临时表中把数据插入到目的表，（这个地方处理重复的数据） 

insert into 目的表 

select 主键.... from 临时表 where 主键 not in（select 主键 from 目的表）