SQL Server BCP 資料匯入匯出

標籤：acl   結束   for   動作   使用者名稱   title   cmdshell   通過   from   

SQL Server BCP 匯入匯出使用

   Bcp 匯出匯入資料高效，比使用SQL Server Management Stdio 提供的資料庫匯出匯入要高效因為sql server 也沒有提供提供類似oracle的expdp和impdp的工具，如果用SQL Server Management Stdio提供的匯入匯出對大表資料進行遷移速度太慢了

   Bcp 匯出資料高效比較適用於大表資料的遷移

進入doc命令列輸入bcp命令，就彈出bcp命令的協助資訊：

C:\Users\wangwei>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 版本]             [-R 允許使用地區設定]

  [-k 保留 Null 值]           [-E 保留標識值]

  [-h"載入提示"]              [-x 產生 xml 格式檔案]

參數介紹：

bcp共有四個動作可以選擇。 

(1) 匯入。 

這個動作使用in命令完成，後面跟需要匯入的檔案名稱。 

(2) 匯出。 

這個動作使用out命令完成，後面跟需要匯出的檔案名稱。 

(3) 使用SQL語句匯出。 

這個動作使用queryout命令完成，它跟out類似，只是資料來源不是表或視圖名，而是SQL語句。 

(4) 匯出格式檔案。 

這個動作使用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的使用者名稱，使用者名稱直接與參數之間不要有空格，否則會報錯

如使用者名稱：Fred, 正確的格式為：-Ufred 

-P password 

指定串連SQL Server的使用者名稱密碼，密碼與參數之間也不要有空格。 

-T 

指定bcp使用信任連接登入SQL Server。如果未指定-T，必須指定-U和-P。 

-k 

指定空列使用null值插入，而不是這列的預設值。 

 

使用bcp匯出資料 

在控制台執行bcp：

bcp db_name.dbo.T_tablename  out c:\a.txt -c -T

bcp db_name.dbo.T_tablename out c:\a.txt -c -Uusername -Pxxxxx

通過調用SQL Server的一個系統預存程序xp_cmdshell以SQL語句的方式運行bcp：

exec master..xp_cmdshell ‘bcp db_name.dbo.T_tablename  out c:\a.txt -c -T‘    --"-T"信任連接

exec master..xp_cmdshell ‘bcp db_name.dbo.T_tablename out c:\a.txt -c -Usa -Pxxxxx‘

注意：如某一參數中間有空格，請用雙引號" 引起來，不是兩個單引號。

說明：

-T指定 bcp 使用網路使用者的安全憑據，通過信任連接串連到 SQL Server。不需要 login_id 和 password。

如果不是使用T需要加上-U和-P參數輸入使用者名稱和密碼

如果是本地匯入匯出可以不加-S參數，如果串連到遠程服務需要加-S參數

bcp db_name.dbo.tablename out d:\table.txt -c -q -S"120.26.76.220,1833" -Usa -Ppassword

 

對要匯出的表進行過濾。 

bcp不僅可以接受表名或視圖名做為參數，也可以接受SQL做為參數，通過SQL語句可以對要匯出的表進行過濾，然後匯出過濾後的記錄。 

EXEC master..xp_cmdshell ‘bcp "SELECT TOP 20 * FROM db_name.dbo.T_tablename" queryout c:\table2.txt -c -U"sa" -P"password"‘ 

bcp還可以通過簡單地設定選項對匯出的行進行限制。 

這條命令使用了兩個參數-F 2和-L 16，表示從SE 

EXEC master..xp_cmdshell ‘bcp "SELECT TOP 20 * FROM db_name.dbo.T_tablename" queryout c:\currency2.txt -F 2 -L 16 -c -U"sa" -P"password"‘ 

SELECT TOP 20 * FROM db_name.dbo.T_tablename所查出來的結果中取第2條到16條記錄進行匯出。

使用bcp匯入資料 

bcp可以通過in命令將bcp匯出的檔案匯入到表中，首先需要建立表結構和匯出資料的表結構相同

在控制台執行bcp：

bcp db_name.dbo.T_tablename  in c:\a.txt-c -T

bcp db_name.dbo.T_tablename in c:\a.txt -c -Uusername -Pxxxxx

通過調用SQL Server的一個系統預存程序xp_cmdshell以SQL語句的方式運行bcp：

exec master..xp_cmdshell ‘bcp db_name.dbo.T_tablename  in c:\a.txt-c -T‘    --"-T"信任連接

exec master..xp_cmdshell ‘bcp db_name.dbo.T_tablename in c:\a.txt -c -Usa -Pxxxxx‘

SQL Server BCP 資料匯入匯出