sqlserver bcp(数据导入导出工具)一般用法与命令详解

bcp是SQL
Server中担负导入导出数据的二个命令行工具,它是依赖DB-Library的,何况能以互相的法门飞快地导入导出大量的多少。bcp能够将数据库的表或视图直接导出,也能透过SELECT
FROM语句对表或视图举办过滤后导出。在导入导出数据时,可以选择暗中认可值或是使用贰个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。上面将详细争辩哪边使用bcp导入导出多少。
1. bcp的首要参数介绍 bcp共有多少个动作能够选用。 (1卡塔尔国 导入。
那个动作使用in命令实现,前边跟须要导入的文件名。 (2卡塔尔 导出。
那几个动作使用out命令完毕,前面跟须要导出的文本名。 (3State of Qatar 使用SQL语句导出。
那一个动作使用queryout命令完结,它跟out相近,只是数据源不是表或视图名,而是SQL语句。
(4State of Qatar 导出格式文件。 那一个动作使用format命令完毕,后而跟格式文件名。
上边介绍部分常用的选项: -f format_file
format_file表示格式文件名。这几个选项正视于上述的动作,假如应用的是in或out,format_file表示早就存在的格式文件,即使选拔的是format则意味着是要调换的格式文件。
-x 那几个选项要和-f format_file合作使用,以便生成xml格式的格式文件。 -F
first_row 钦命从被导出表的哪一行导出,或从被导入文本的哪一行导入。 -L
last_row
钦命被导出表要导到哪一行停止,或从被导入文本导数据时,导到哪一行甘休。
-c
使用char类型做为存款和储蓄类型,未有前缀且以”\t”做为字段分割符,以”\n”做为行分割符。
-w
和-c近似,只是当使用Unicode字符集拷贝数据时行使,且以nchar做为存款和储蓄类型。
-t 田野(field卡塔尔国_term 内定字符分割符,暗中认可是”\t”。 -r row_term
钦命行分割符,默许是”\n”。 -S server_name[ \instance_name]
内定要三番若干次的SQL Server服务器的实例,假设未钦点此选项,bcp连接本机的SQL
Server暗许实例。固然要延续某台机械上的暗中同意实例,只供给钦点机器名就能够。
-U login_id 钦点连接SQL Sever的客户名。 -P password 钦点连接SQL
Server的顾客名密码。 -T 内定bcp使用信赖连接登入SQL
Server。假使未钦定-T,必得内定-U和-P。 -k
钦命空列使用null值插入,而不是那列的暗中同意值。 2. 如何使用bcp导出多少
(1卡塔尔(قطر‎ 使用bcp导出全方位表或视图。 bcp AdventureWorks.sales.currency out
c:\currency1.txt -c -U”sa” -P”password” –使用密码连接或bcp
AdventureWorks.sales.currency out c:\currency1.txt -c -T –行使信赖连接
下边是上述命令实行后的出口结果 Starting copy… 105 rows copied. Network
packet size (bytes卡塔尔: 4096 Clock Time (ms.卡塔尔(قطر‎ Total : 10 Average :
(10500.00 rows per sec.卡塔尔(قطر‎ 上边是currency1.txt的一对内容 AED Emirati
Dirham 1996-06-01 00:00:00.000 AFA Afghani 壹玖玖陆-06-01 00:00:00.000 …
… … … … … ZWD Zimbabwe Dollar 一九九六-06-01 00:00:00.000
在应用密码登陆时索要将-U后的客商名和-P后的密码加上双引号。
注:bcp除了能够在调整台实施外,仍是可以通过调用SQL
Server的叁个体系存款和储蓄进程xp_cmdshell以SQL语句的艺术运维bcp。如上述第一条命令可改写为
EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency out
c:\currency1.txt -c -U”sa” -P”password”‘
执行xp_cmdshell后,再次来到音信以表的款式出口。为了能够方便地在SQL中推行bcp,上边包车型客车指令都应用xp_cmdshell施行bcp命令。
(2卡塔尔国 对要导出的表张开过滤。
bcp不只好够选拔表名或视图名做为参数,也能够担任SQL做为参数。通过SQL语句能够对要导出的表举行过滤,然后导出过滤后的笔录。
EXEC master..xp_cmdshell ‘bcp “SELECT TOP 20 * FROM
AdventureWorks.sales.currency” queryout c:\currency2.txt -c -U”sa”
-P”password”‘ bcp还足以因而轻松地设置选项对导出的行进行约束。
那条命令使用了三个参数-F 10和-L 13,表示从SE EXEC master..xp_cmdshell
‘bcp “SELECT TOP 20 * FROM AdventureWorks.sales.currency” queryout
c:\currency2.txt -F 10 -L 13 -c -U”sa” -P”password”‘ LECT TOP 20 *
FROM
AdventureWorks.sales.currency所查出来的结果中取第10条到13条记下实行导出。3.
怎么运用bcp导出格式文件

bcp不只可以够依照表、视图导入导出多少,还足以合营格式文件对导入导出数据举行节制。格式文件以纯文本文件格局存在,分为经常格式和xml格式。客商能够手工业编写制定格式文件,也得以由此bcp命令遵照表、视图自动生成格式文件。
EXEC master..xp_cmdshell ‘bcp AdventureWorks.sales.currency format nul
-f c:\currency_format1.fmt -c -T’
上述命令将currency表的结构变迁了多个格式文件currency_format1.fmt,上面是以此格式文件的内容。
9.0 3 1 SQLCHA昂Cora 0 6 “\t” 1 CurrencyCode
SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 100 “\t” 2 Name
SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 24 “\r\n” 3 ModifiedDate
那几个格式文件记录了这几个表的字段类型、长度、字符和行分割符和字段名等新闻。
bcp还足以经过-x选项生成xml格式的格式文件。 EXEC master..xp_cmdshell
‘bcp AdventureWorks.sales.currency format nul -f
c:\currency_format2.fmt -x -c -T’
xml格式文件所描述的源委和平时个性式文件所叙述的内容完全相似,只是格式差别。
4. 如何行使bcp导入数据
bcp能够透过in命令将方面所导出的currency1.txt和currency2.txt再重复导入到数据库中,由于currency有主键,由此大家将复制贰个和currency的布局完全同样的表。
SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM
AdventureWorks.sales.currency 将数据导入到currency1表中 EXEC
master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:\currency1.txt -c -T’
导入数据也同等能够利用-F和-L选项来采撷导入数据的记录行。 EXEC
master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:\currency1.txt -c -F 10 -L 13 -T’
在导入数据时得以依照已经存在的格式文件将满意条件的笔录导入到数据库中,不满意则不导入。如上述的格式文件中的第多少个字段的字符长度是24,假如有些文本文件中的相应字段的长短超越24,则那条记下将不被导入到数据库中,别的满意条件的记录健康导入。
使用普通的格式文件 EXEC master..xp_cmdshell ‘bcp
AdventureWorks.sales.currency1 in c:\currency1.txt -F 10 -L 13 -c -f
c:\currency_format1.fmt -T’ 使用xml格式的格式文件 EXEC
master..xp_cmdshell ‘bcp AdventureWorks.sales.currency1 in
c:\currency1.txt -F 10 -L 13 -c -x -f c:\currency_format2.fmt -T’
总结 bcp命令是SQL
Server提供的三个高效的数目导入导出工具。使用它没有须要运行任何图形处理工具就能够以高速的方法导入导出数据。当然,它也能够透过xp_cmdshell在SQL语句中试行,通过这种艺术能够将其放置顾客端程序中运作,那也是使顾客端程序具有数据导入导出功能的不二等秘书技之一。
bcp命令详明(sybase卡塔尔(قطر‎ bcp 实用工具在 Microsoft SQL Server 二零零三实例和数据文件之间以客户钦赐的格式复制数据。 语法 bcp
{[[database_name.][owner].]{table_name | view_name} | “query”}
{in | out | queryout | format} data_file [-m max_errors] [-f
format_file] [-e err_file] [-F first_row] [-L last_row] [-b
batch_size] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
[-q] [-C code_page] [-t field_term] [-r row_term] [-i
input_file] [-o output_file] [-a packet_size] [-S
server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h “hint [,…n]”] 参数
database_name
钦定的表或视图所在数据库的称呼。固然未钦定,则为顾客私下认可数据库。 owner
表或视图全数者的名称。假设实践大体积复制操作的客户全部钦赐的表或视图,则
owner 是可选的。若无一些名 owner
而且实行大体积复制操作的顾客不享有钦点的表或视图,则 Microsoft? SQL
Server? 贰零零零 将回到错误新闻并收回大容积复制操作。 table_name
是将数据复制到 SQL Server 时 (in卡塔尔 的指标表名,以至从 SQL Server
复制数据时 (out卡塔尔 的源表名。 view_name 是将数据复制到 SQL Server 时 (in卡塔尔(قطر‎的目标视图名,以至从 SQL Server 复制数据时 (out卡塔尔的源视图名。独有内部具有列都引用同叁个表的视图技术用作指标视图。有关将数据复制到视图的界定的越多音信,请参见
INSERT。 Query 是回到叁个结果集的 Transact-SQL
查询。若是查询重回八个结实集,比如钦定 COMPUTE 子句的 SELECT
语句,只有首先个结果集将复制到数据文件,随后的结果集被忽视。使用双引号引起查询语句,使用单引号引起查询语句中存放的任何内容。在从询问中山高校体积复制数据时,还非得钦赐queryout。 in | out | queryout | format 内定大容量复制的自由化。in
是从文件复制到数据库表或视图,out
是指从数量库表或视图复制到文件。唯有从询问中山大学体量复制数据时,才必得内定queryout。根据钦点的选项以致表或视图分隔符,format
将创立三个格式文件。要是应用 format,则还必须内定 -f 选项。 表达Microsoft SQL Server 6.5 中的 bcp 实用工具不协助大体量复制到包蕴sql_variant 或 bigint 数据类型的表。 data_file
大体量复制表或视图到磁盘时所用数据文件的总体路线。当将数据大体积复制到
SQL Server 时,此数据文件包括将复制到钦命表或视图的数据。当从 SQL Server
大容积复制数据时,该数据文件满含从表或视图复制的数目。路线能够有 1 到
255 个字符。 -m max_errors
钦赐在大容积复制操作撤除以前可能发生的失实的最大额。bcp
不能复制的每一行都将被忽略并计为多个不当。若无包涵该选用,则默以为10。 -f format_file
钦命格式文件的一体化路线,该格式文件包涵曾在同三个表或视图上使用 bcp
时的寄放响应。当使用由 format
选项所开创的格式文件大体积复制入或复制出数据时,使用此选项。格式文件的始建是可选的。在晋升多少个格式难点之后,bcp
将唤起是还是不是在格式文件中保留回答。暗中认可文件名字为Bcp.fmt。大体量复制数据时,bcp
可引用三个格式文件,因而不要再一次交互作用输入从前的作答。即便未使用此选项,也从不点名
–n、-c、-w、-6 或 -N,则 bcp 将唤起输入格式音信。 -e err_file
钦定错误文件的总体路线,此错误文件用于存款和储蓄 bcp
无法从文件传输到数据库的全数行。来自 bcp
的错误音信将发送到客商工作站。借使未使用此选项,则不创制错误文件。 -F
first_row 内定要大容积复制的首先行的序数。私下认可值是
1,表示在钦点数据文件的率先行。 -L last_row
内定要大容积复制的结尾一行的序数。暗中同意值是
0,表示钦赐数据文件中的最终一行。 -b batch_size
钦赐所复制的每批数量中的行数。每个批管理作为三个事情复制至服务器。SQL
Server
提交或回滚每种批管理的政工。暗中同意情形下,钦点的数据文件中的全体数据都看成一堆复制。请不要与
-h “ROWS_PER_BATCH = bb” 选项联合行使。 -n
使用数据的本机数据类型实践大体积复制操作。此选项不升迁输入每一字段,它将选拔本机值。
-c 使用字符数据类型推行大容积复制操作。此选项不提示输入每一字段;它使用
char 作为存款和储蓄类型,不带前缀,\t作为字段分隔符,\n作为行终止符。 -w
使用 Unicode 字符试行大体积复制操作。此选项不升迁输入每一字段;它选拔nchar 作为存款和储蓄类型,不带前缀,\t作为字段分隔符,\n作为行终止符。不能够在
SQL Server 6.5 版或更早版本中采纳。 -N
对非字符数据应用数据的本机数据类型和对字符数据利用 Unicode
字符类型试行大体积复制操作。那是可代表 -w
选项的品质更加高的选项,其目标是应用数据文件将数据从三个 SQL Server
传输到另三个 SQL Server 中。它不晋升输入每一字段。在供给传输包罗 ANSI
增添字符的多少以致想接受本机格局的品质时,能够运用这一选项。不能够在 SQL
Server 6.5 版或更早版本中央银行使 -N 选项。 -V (60 | 65 | 70卡塔尔国 使用 SQL
Server 开始时期版本中的数据类型试行大体量复制操作。此选项与字符 (-c卡塔尔 或本机
(-n卡塔尔格式一同利用。此选项并不升迁输入每一字段,它应用默许值。比方,若要将 SQL
Server 6.5 中的 bcp 实用工具所支撑的日子格式大容积复制到 SQL Server
2002,可采纳 -V 65 参数。 首要 将数据从 SQL Server
大体量复制到数据文件时,即使钦定了 –V,bcp 实用工具也不会为其余 datetime
或 smalldatetime 数据生成 SQL Server 6.0 或 SQL Server 6.5
的日子格式。日期将一贯以 ODBC 格式写入。其余,由于 SQL Server 6.5
版或更早版本不扶持可为空的 bit 数据,由此 bit 列中的空值写为值 0。 -6
使用 SQL Server 6.0 或 SQL Server 6.5
数据类型实行大体积复制操作。仅为维持向后包容性。改为运用 –V 选项。 -q 在
bcp 实用工具和 SQL Server 实例的连小刑试行 SET QUOTED_IDENTIFIE路虎极光S ON
语句。使用该选项钦命蕴含空格或引号的数据库、全体者、表或视图的称号。将由三局地构成的所有事表名或视图名引在双引号
(” “卡塔尔 中。 -C code_page
仅为保持向后宽容性。作为代替,请在格式文件或交互作用式 bcp
中为每一列内定八个排序法则名。
钦点数据文件中的数据代码页。唯有当数码中包罗字符值大于 127 或低于 32 的
char、varchar 或 text 列时,code_page 才有用。 代码页值 描述 ACP
ANSI/Microsoft Windows? (ISO 1252卡塔尔(قطر‎。 OEM
客商程序使用的暗中认可代码页。假诺未钦赐 -C,则那是 bcp 使用的暗中同意代码页。
RAW
不发生从贰个代码页到另四个代码页的转移。因为不发出转换,所以那是最快的选项。
值 特定的代码页号码,譬喻 850。 -t 田野先生_term
钦赐字段终止符。暗中同意的字段终止符是 \t。使用此参数替代私下认可字段终止符。
-r row_term 钦点行终止符。默许的行终止符是
\n。使用此参数代替默许行终止符。 -i input_file
钦命响应文件的称号,使用交互作用情势施行大体积复制时,响应文件富含对每一字段命令提醒难点的响应。
-o output_file 钦命选取 bcp 输出的文书的称呼。 -a packet_size
内定发送到和出殡和安葬自服务器的种种网络数据包的字节数。能够采用 SQL Server
公司微机设置服务器配置选项。然则,使用此选项能够单个地替代服务器配置选项。packet_size
能够安装为 4096 到 65535 字节,暗中认可值为 4096。
数据包大小的加多能够巩固大体积复制操作的个性。假使必要多个不小的数据包而得不到,则使用暗中同意设置。bcp
生成的质量总括突显出所使用数据包的分寸。 -S
server_name[\instance_name] 钦命要延续到的 SQL Server 实例。钦定server_name 以三番两次该服务器上的 SQL Server 暗中认可实例。钦点server_name\instance_name 以三回九转到该服务器上的 SQL Server 二〇〇〇命名实例。假如未钦命服务器,则 bcp 连接到地头计算机上的 SQL Server
暗许实例。从网络上的长间隔Computer实践 bcp 时,须求此选项。 -U login_id
内定用于连接到 SQL Server 的报到 ID。 -P password 内定登入 ID
的密码。借使未利用此选项,则 bcp
将唤起输入密码。即使不带密码将此选项用于命令提醒行末尾,则 bcp
将接收暗中认可密码 (NULLState of Qatar。 -T 内定 bcp
使用网络客户的辽阳凭据,通过信赖连接连接到 SQL Server。无需 login_id
和 password。 -v 报告 bcp 实用工具的版本号和版权。 -大切诺基钦赐使用为客户端计算机的区域设置定义的区域格式,将货币、日期和时间数额大体积复制到
SQL Server 中。私下认可情状下,将会忽略区域安装。 -k
钦命在大体积复制操作中空驶列车应封存叁个空值,实际不是对插入的列给予暗中认可值。bcp
的貌似用法
用法: bcp {dbtable | query} {in | out | queryout | format}
数据文件 [-m 最大错误数] [-f 格式化文件] [-e 错误文件] [-F 首行]
[-L 末行] [-b 批大小] [-n 本机类型] [-c 字符类型] [-w
宽字符类型] [-N 将非文本保持为本机类型] [-V 文件格式版本] [-q
带引号的标志符] [-C 代码页表明符] [-t 字段终止符] [-r 行终止符]
[-i 输入文件] [-o 输出文件] [-a 数据包大小] [-S 服务器名称] [-U
用户名] [-P 密码] [-T 可信赖连接] [-v 版本] [-牧马人 允许选择区域安装]
[-k 保留空值] [-E 保留标志值] [-h”加载提醒”] [-x 生成xml
格式化文件] 导入csv格式文件 Exec master..xp_cmdshell ‘bcp
“SSIS.dbo.tb2” in “E:\export.csv” -c -t”,” -r”\n” -T’ 导出成csv Exec
master..xp_cmdshell ‘bcp “SSIS.dbo.tb2” out “E:\test.csv” -c -t”,”
-r”\n” -T’ 将一定查询导出成暗许格式 暗许以制表符间距,”\n”换行 Exec
master..xp_cmdshell ‘bcp “select carbrand,longitude from ssis.dbo.tb2”
queryout “E:\test2.txt” -c -T’ 其余情状参谋: 启用xp_cmdshell EXEC
sp_configure ‘show advanced options’, 1 GO RECONFIGURE GO EXEC
sp_configure ‘xp_cmdshell’, 1 GO RECONFIGURE GO

发表评论

电子邮件地址不会被公开。 必填项已用*标注