`
csstome
  • 浏览: 1468138 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQL server 数据导入导出BCP工具使用详解

 
阅读更多

数据的导入导出是数据库管理员常见的工作任务之一,尤其是平面文件的导入导出。BCP 工具则为这些任务提供了强有力的支持,它是基于DB-Library,尤其是在生产环境中,从本地传送数据到服务器或从服务器传送数据到本地,因它无需提供图形界面,减少网络带宽,提高了传输速率。BCP的全称是BULK COPY PROGRAM,它是一个命令行程序,可以完全脱离SQL server进程来实现。


常用的导入方式:bcp, BULK INSERT,OPENROWSET,or SSIS。

本文主要介绍bcp工具的使用,其他的如BULK INSERT,OPENROWSET,or SSIS请参见后续博文。


bcp的使用:可以在SQL Server 2005 实例和用户指定格式的数据文件间实现大容量复制数据,可以将平面文件导入到SQL server表,也可以将SQL server表导出为文件。该命令为一个DOS命令,通常位于x:/Program Files/Microsoft SQL Server/90/Tools/Bin目录下,可以在命令提示符下使用。

以下简要列出其语法:
语法:bcp {[[database_name.][owner].]{table_name | view_name} | "query"} --指定相应的数据库名,表名,视图名或SQL查询语句,查询语句使用双引号括起来。


{in | out | queryout | format} data_file --数据流动的方向,in导入,out导出,queryout结果集 及指定data_file文件。
[-mmax_errors] [-fformat_file] [-x] [-eerr_file] --bcp的错误处理选项
[-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]"] --hint提示使用TABLOCK或CHECK_CONSTRAINTS,FIRE_TRIGGERS 等通常用于支持最小日志记录

几个常用的参数:-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值插入,而不是这列的默认值。

权限:
bcp out 操作要求对源表有 SELECT 权限。
bcp in 操作要求至少对目标表有 SELECT/INSERT 权限。
当被导入的表中存在CHECK约束和TRIGGER时,缺省的行为为关闭,不要指定-h 选项和 CHECK_CONSTRAINTS 以及FIRE_TRIGGERS 提示。故需要对表有Alter table 权限。


一、bcp导出到平面文件
1.将表复制到平面文件(使用信任连接,使用参数 -T),在命令提示符下输入以下语句

bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders.txt -c -T -- -T表示使用可信任的连接

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
--.........省略
--.........省略
31465 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 454 Average : (69306.17 rows per sec.)

使用xp_cmdshell存储过程来执行bcp,鉴于演示的方便,后续的处理将全部采用xp_cmdshell来实现。
EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders1.txt -c -T'

2.将表复制到平面文件(使用混合身份验证,使用参数 -U —P,例如:-U"Test" -P"Test" , -U后的用户名和-P后的密码加上双引号)
EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders2.txt -c -U"Test" -P"Test"'

3.将指定的列或行复制到平面文件
EXEC xp_cmdshell --导出指定的列 使用到了queryout
'bcp "SELECT SalesOrderID,OrderDate,SalesOrderNumber FROM AdventureWorks.Sales.SalesOrderHeader" queryout d:/SalesOrders3.txt -c -U"Test" -P"Test" '

EXEC xp_cmdshell --导出指定的行,查询结果中的第20到第40条记录, 使用到了queryout
'bcp "SELECT TOP(50) SalesOrderID,OrderDate,SalesOrderNumber FROM AdventureWorks.Sales.SalesOrderHeader WHERE OrderDate >= ''20040101''" queryout d:/SalesOrders4.txt -c -F 20 -L 40 -T '

二、bcp导出格式文件
格式文件分为一般格式文件和XML格式文件,以下示例将SalesOrderHeader表的格式形成一个一般格式文件,也称为非XML 格式化文件。
EXEC xp_cmdshell 'bcp AdventureWorks.sales.SalesOrderHeader format nul -f d:/SalesOrders_format.fmt -c -T '
----------------
9.0
27 --字段总数,多出的字段被省略,以下分别给出了字段的序号,类型,长度,分隔符,字段名等信息。
1 SQLCHAR 0 12 "/t" 1 SalesOrderID ""
2 SQLCHAR 0 5 "/t" 2 RevisionNumber ""
3 SQLCHAR 0 24 "/t" 3 OrderDate ""
4 SQLCHAR 0 24 "/t" 4 DueDate

""
XML 格式化文件
EXEC xp_cmdshell 'bcp AdventureWorks.sales.SalesOrderHeader format nul -x -f d:/SaOrders_format_x.xml -c -T '
非XML格式化文件与XML格式化文件两者用不同的方式来描述原始表的结构,其实质是一样的。

bcp导入平面文件到数据库表
创建新表NewOrderHeader,然后将前面导出的数据导入到新表


SELECT * INTO NewOrderHeader FROM sales.SalesOrderHeader WHERE 1=2
EXEC [master]..xp_cmdshell 'bcp AdventureWorks..NewOrderHeader in d:/SalesOrders.txt -c -T'
SELECT * FROM NewOrderHeader

使用格式化文件实现bcp的大容量导入

TRUNCATE TABLE NewOrderHeader
EXEC [master]..xp_cmdshell 'bcp AdventureWorks..NewOrderHeader in d:/SalesOrders.txt -f d:/Currency.xml -F 2000 -L 4000 -c -T'
SELECT * FROM NewOrderHeader

分享到:
评论

相关推荐

    SQL_Server数据导入导出工具BCP详解

    SQLServer 数据 导入导出 BCP详解。。。。。。。。。

    bcp—SQLServer命令行数据导入导出工具使用总结.txt

    bcp是微软SQLServer数据库系统的命令行工具,用于进行大数据量的导入导出,该工具简单实用、效率极高,本文总结了bcp命令操作的各种设置开关功能含义和使用方法,可以为需要的用户提供有益帮助。

    sql server 中 BCP使用方法详解

    sql server 中 BCP使用方法详解,快速从SQL SERVER中导入导出数据的命令

    sql server数据导入导出

    sql server数据导入导出 指定要连接的SQL Server服务器的实例,如果未指定此选项,bcp连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。

    sql server 2008 导入导出数据大全

    sql server 2008 导入导出数据大全 /******* 导出到excelEXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q - S"GNETDATA/GNETDATA" -U"sa" -P""' /*********** 导入ExcelSELECT * ...

    SQL Server中导入导出数据三方法比较

    在SQL Server中主要有三种方式导入导出数据:使用Transact-SQL对数据进行处理;调用命令行工具BCP处理数据;使用数据转换服务(DTS)对数据进行处理。这三种方法各有其特点,下面就它们的主要特点进行比较。

    Sql Server数据库BCP导入导出工具

    数据导出:在ServerDbConfig.cfg文件里配置好数据库访问所需要的信息,在sql文件下配置好要导出的SqlFiles,执行批处理DBUpdator_Normal.bat,就可以获得导出数据的文本形式,同样的配置导入工具的相关信息,...

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

    bcp是SQL Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以并行的方式高效地导入导出大批量的数据。bcp可以将数据库的表或视图直接导出,也能通过SELECT FROM语句对表或视图进行过滤后...

    BCP命令导入导出数据

    BCP批量导入excel数据到sqlserver相关资料整理

    sql server bcp 导入 到处 详细资料 参数

    sql server bcp 导入 到处 详细资料 参数

    bcp导入导出数据

    bcp导入导出SQL Server和Sybase数据库数据

    sqlserver bcp导出到excel

    sqlserver bcp导出到excel 测试可用

    SQL Server数据表导出器

    SQLDBExport(SQL Server数据表导出器)是一套用来完成将SQL Server2000数据库中的表结构导出到Excel 2000,并输出标准的打印报表格式的软件。 软件采用Excel 2000 中VBA开发完成,软件使用Excel中的菜单操作,与...

    BCP数据导入导出实例讲解

    批量复制程序(BCP)是Microsoft SQL Server中的一个命令行实用程序。使用BCP,我们可以导入并可从SQL Server数据库中快速而轻松地导出数据。任何使用这个功能的数据库管理员都承认BCP是一个关键工具。

    SQL2005的bcp命令-批量快速导入导出数据

    SQL2005的bcp命令-批量快速导入导出数据,公司内部人员写的资料,希望对大家有帮助

    sql server 通过BCP导出几张表内容

    可以通过批处理 定期导出数据库里面表的系信息

    bcp工具Windows

    批量导入导出工具,无需安装数据库即可使用,,,,,

    SQLServer导出数据到MySQL实例介绍

    1从SQLServer导出数据 执行BCP: bcp “…” queryout “F:\test.txt” -c –S1.2.3.4 -Usa -P1111 命令参数含义: (1) 导入。 这个动作使用in命令完成,后面跟需要导入的文件名。 (2) 导出。 这个动作使用out命令...

    SQL Server中将数据导出为XML和Json方法分享

    这种需求对于SSIS来说当然是非常简单,但很多时候仅仅需要一次性导出这些数据而建立一个SSIS包就显得小题大做,而SQL Server的导入导出工具其中BUG还是蛮多的,最简单的办法是BCP。  数据导出为XML  在SQL Server ...

Global site tag (gtag.js) - Google Analytics