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

284354749

　　SQL server 数据导入导出BCP工具使用详解
　　From: http://blog.csdn.net/leshami/article/details/5312858
　　数据的导入导出是数据库管理员常见的工作任务之一，尤其是平面文件的导入导出。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>　　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
　　-------------------------------------------------------------------------------------------------------
　　总结：
　　-- Problem:
　　EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders2.txt -c -U"Test" -P"Test"'
　　SQLState=28000,NativeError=18456
　　Error=[Microsoft][SQL Server Native Client 10.0][SQL Server]login failed for user 'TEST'
　　--Root cause
　　1. New login , sql server authentication.(-U"Test" -P"Test"), run the below query cause the issue.
　　EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders2.txt -c -U"Test" -P"Test"'
　　2. it is caused by the server just use the Windows Authentication mode, can't run the above query.
　　-- Solution:
　　1. changed to SQL Server and Windows Authentication mode, and restart sql service,it works fine.
　　EXEC xp_cmdshell 'bcp AdventureWorks.Sales.SalesOrderHeader OUT d:/SalesOrders2.txt -c -U"RICKYDOM\Administrator" -P"Test"'
　　2. It also didn't work, as you should change to mixed authentication mode, and use sql authentication user when use -U/-P parameters.