資料的導入導出是資料庫管理者常見的工作任務之一,尤其是平面檔案的導入導出。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'