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