BCP命令SQL server数据快速导入导出.docx
《BCP命令SQL server数据快速导入导出.docx》由会员分享,可在线阅读,更多相关《BCP命令SQL server数据快速导入导出.docx(14页珍藏版)》请在冰豆网上搜索。
![BCP命令SQL server数据快速导入导出.docx](https://file1.bdocx.com/fileroot1/2023-2/22/a265b66c-608b-4539-9a5b-5587d3572ac4/a265b66c-608b-4539-9a5b-5587d3572ac41.gif)
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 目的表)