SQL Server中bcp命令的用法以及資料大量匯入匯出SQL Server BCP使用小結某社區600萬使用者資料匯入MYSQL、MSSQL、Oracle資料庫方法某社區600萬使用者資料匯入MYSQ

文章目錄

• 2.1.將表中資料匯出到一個檔案中（使用可信串連）
• 2.2.將表中資料匯出到一個檔案中（使用混合模式身分識別驗證）
• 2.3.將檔案中的資料匯入到表中

0.參考文獻：

SQL Server BCP使用小結

bcp Utility

某社區600萬使用者資料匯入MYSQL、MSSQL、Oracle資料庫方法

SELECT INTO 和 INSERT INTO SELECT 兩種表複製語句

1.bcp命令參數解析

bcp命令有許多參數，下面給出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 保留空值]               [-E 保留標識值]  [-h"載入提示"]              [-x 產生xml 格式檔案]

其中最常用的已經用粉紅色字型標註。

2.bcp命令執行個體

這裡我們以AdventureWorks範例資料庫為例進行實驗。

2.1.將表中資料匯出到一個檔案中（使用可信串連）

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c

上面的參數 out 表示輸出檔案，c:\Currency.dat是檔案名稱和路徑，-T表示可信串連，這個跟sqlcmd有點不同，在sqlcmd中使用-E表示可信串連。-c表示以字元形式輸出，如果使用-w的話，輸出內容相同，但是輸出檔案的大小將增加一倍。如果你要將匯出的Currency.dat檔案匯入到非sql server資料庫中，那麼使用-w比較好。

2.2.將表中資料匯出到一個檔案中（使用混合模式身分識別驗證）

bcp AdventureWorks.Sales.Currency out c:\Currency.dat -c -Usa -Psa12345 -S.

這個資料匯出語句與前面的不同之處是，前面使用可信串連，也就是windows驗證，不需要輸入使用者名稱和密碼。而此時使用的是sql server 驗證方式，所以得輸入sql server資料庫使用者名稱與密碼。這裡-S表示要串連的資料來源，我這裡-S.表示串連本地的預設執行個體，如果不加-S這個參數也會串連到預設執行個體中，如果要串連具名執行個體的話，可以使用參數：-S<server_name\instance_name>

2.3.將檔案中的資料匯入到表中

在bcp Utility中提到：

“如果使用 bcp 備份資料，請建立一個格式檔案來記錄資料格式。 bcp 資料檔案不包括任何架構或格式資訊，因此如果已刪除表或視圖並且不具備格式檔案，則可能無法匯入資料。”

這句話的意思是，假如你要使用bcp來備份資料的話，那麼最好也將資料的表結構也跟資料一起匯出來。這樣的話即使表被刪除了，也可以通過先建立表，然後再使用bcp匯入資料的方法進行還原。但是如果你只是用bcp備份資料，而沒有備份表結構，那麼當表被刪除以後，你將無法使用bcp匯入資料。

更深層的意思就是，如果你要使用bcp匯入資料，那麼必須有表結構。這個類似於insert into select的複製操作，因為它也需要先建立好表，然後再進行資料備份。具體可以參考：SELECT INTO 和 INSERT INTO SELECT 兩種表複製語句。

所以假如我們要將前面匯出的Currency.dat匯入到資料庫中，那麼資料庫中必須有對應的一張表，我們這裡建立一張叫做Sales.Currency2的空表，sql語句如下： 

USE AdventureWorks;GOSELECT * INTO Sales.Currency2 FROM AdventureWorks.Sales.Currency WHERE 1=2;--只建立表結構而不會插入資料

在建立好表結構以後，就可以將本地檔案中的資料匯入到資料庫表中，匯入的bcp命令如下:

bcp AdventureWorks.Sales.Currency2 in c:\Currency.dat -T -c

2.4.bcp中使用queryout關鍵詞

如果要根據某種條件來匯出資料的話，可以使用queryout關鍵字。

2.4.1.將特定的列複製到資料檔案中

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout c:\Currency.Name.dat -T -c

2.4.2.將特定的行複製到資料檔案中

bcp "select * from AdventureWorks.Sales.Currency where CurrencyCode='AED' and Name='Emirati Dirham'" queryout c:\Currency3.dat -T -c

3.大資料量的大量匯入

bcp本身就可應用於大資料量的大量匯入，不過他是命令列形式，如果要使用sql命令進行大資料量的大量匯入，可以使用bulk insert，這個在之前的一篇部落格中有提到，並進行了實驗，可以參考：某社區600萬使用者資料匯入MYSQL、MSSQL、Oracle資料庫方法。另外還找了一篇部落格，裡面列出了bulk insert和bcp的大量匯入方式，詳細請參考：SQL Server BCP使用小結。

bcp並不只是只能在cmd命令列中執行，也可以在sql查詢語句中執行，不過這需要調用一個預存程序。比如前面的將資料庫表的資料匯出到一個檔案中，可以在SSMS中執行如下sql語句

exec master..xp_cmdshell 'bcp AdventureWorks.Sales.Currency out c:\Currency.dat -T -c'

假如你在執行上述語句的時候報如下錯誤:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

你可以通過執行如下語句來解決問題：參考：xp_cmdshell Option

-- To allow advanced options to be changed.EXEC sp_configure 'show advanced options', 1GO-- To update the currently configured value for advanced options.RECONFIGUREGO-- To enable the feature.EXEC sp_configure 'xp_cmdshell', 1GO-- To update the currently configured value for this feature.RECONFIGUREGO