# 如何使用 bcp 對 SQL Server 資料表進行批次匯出與匯入 要匯入大量資料到資料庫中，其實有[很多方法](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)，其中一個方法則是透過 [bcp](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) 工具程式 (bulk copy program)，不過這個命令列工具跟一般 CLI 工具的參數用法有很大的不同，所以這篇文章我就來介紹一下如何使用 bcp 來匯出與匯入 SQL Server 中的資料。 ![bcp](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/fb5ef1ae9028a59bccd2-68b8834e-c587-44d3-80f2-e05257ccb40b.webp) ### 基本語法 bcp 的基本語法如下： ``` bcp {資料表、檢視表或查詢語法} {IN | OUT | QUERYOUT | FORMAT} {資料檔案} {選項} ``` 官網的文件是這樣描述的： ``` bcp [database_name.] schema.{table_name | view_name | "query"} {in data_file | out data_file | queryout data_file | format nul} [-a packet_size] [-b batch_size] [-c] [-C { ACP | OEM | RAW | code_page } ] [-d database_name] [-D] [-e err_file] [-E] [-f format_file] [-F first_row] [-G Azure Active Directory Authentication] [-h"hint [,...n]"] [-i input_file] [-k] [-K application_intent] [-l login_timeout] [-L last_row] [-m max_errors] [-n] [-N] [-o output_file] [-P password] [-q] [-r row_term] [-R] [-S [server_name[\instance_name]]] [-t field_term] [-T] [-U login_id] [-v] [-V (80 | 90 | 100 | 110 | 120 | 130 | 140 | 150 | 160 ) ] [-w] [-x] ``` 由於參數用法很多，容易眼花撩亂，以下我就盡量用「範例」來解說各種用法。 ### 基本語法範例 我以一個簡單的「匯出資料」範例開始，如下範例： ```powershell bcp "ContosoUniversity.dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -c ``` 其中 - `ContosoUniversity` 是「資料庫名稱」 - `dbo` 是「結構描述名稱」 - `Course` 是「資料表名稱」 - `OUT` 是指要「匯出資料」 - `G:\BCP\Course.bcp` 是「匯出資料的檔案路徑」 - `-S"(localdb)\MSSQLLocalDB"` 是「伺服器位址」 - `-T` 是「使用 Windows 認證」或「信任式連線」 - `-c` 是「使用字元模式」 同樣的匯出指令，也可以寫成這樣，將第一個參數的**資料庫名稱**忽略，改用 `-d` 參數指定**資料庫名稱**： ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c ``` 如果想執行「任意 T-SQL 查詢」來匯出資料的話，也非常簡單，將 `OUT` 改成 `QUERYOUT` 輸出即可，如下範例： ```powershell bcp "SELECT [Title],[Credits] FROM [dbo].[Course]" QUERYOUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d" ContosoUniversity" -c ``` 若要連接 Azure SQL Database 的話，由於不支援信任式連線，所以要改用帳號密碼連線，如下範例： ```powershell bcp "[dbo].[MUM_M]" OUT "G:\BCP\MUL_M_character.bcp" -S"my.database.windows.net" -U"myairdbadmin" -P"AO7zbuS0$Dcg" -d"AIRDB" -c ``` 其中 - `-S"my.database.windows.net"` 是指定「伺服器位址」 - `-U"myairdbadmin"` 是指定「SQL Server 登入帳號」 - `-P"AO7zbuS0$Dcg"` 是指定「SQL Server 登入密碼」 - `-d"AIRDB"` 是指定「資料庫名稱」 看到這裡，你應該可以意識到「選項」的使用方式比較特別，也就是參數(如:`-d`)後面的參數值(如:`AIRDB`)之間，並沒有用一個「空白字元」分隔喔！ 如果想要留下匯出或匯入過程的記錄檔，也可以這樣下指令： ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -e"G:\BCP\Error_out.log" -o"G:\BCP\Output_out.log" ``` 其中 - `-e"G:\BCP\Error_out.log"` 是指定「錯誤記錄檔」 - `-o"G:\BCP\Output_out.log"` 是指定「輸出記錄檔」 ### 匯出資料 由於「匯出資料」會牽涉到儲存格式的問題，你怎樣儲存匯出檔，決定了你未來可否順利的匯入資料。 一般來說，我們可以區分兩種儲存模式： 1. 字元模式 (`-c`) (character mode) 透過 bcp 匯出資料，若選擇「字元模式」的話，匯出資料會以「字元」的方式儲存，這樣的好處是，你可以直接用文字編輯器開啟檔案，檢視資料內容。但缺點是，若資料內容有「分隔符號」的話，就容易造成資料錯亂。若你確定資料表中的資料不會有奇怪的分隔字元，就可以用字元模式來匯出。 字元模式預設會用 `\t` (Tab) 符號代表 field separator (欄位分隔符號)，並以 `\r\n` 做為 row terminator (資料列分隔符號)。 採用字元模式另一個需要考量的問題，就是輸出的字集 (character set) 問題，預設的字集是根據作業系統的語系而定。假設我們要匯出 `ContosoUniversity` 資料庫中的 `dbo.Course` 表格，並使用 `-c` 字元模式輸出，我的作業系統由於設定的語系是「繁體中文(台灣)」，所以預設採用的字集將會是 `950` (Big5) 輸出，這可能會在使用不同電腦匯入資料時遇到問題。 ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c ``` 但你也可以用 `-C` (大寫的 C 喔) 指定字集，例如指定 `65001` (`UTF-8`) 輸出，如下範例： ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C 65001 ``` 也可以使用 `-w` 參數，指定使用 Unicode 字元模式，他預設會用 `Unicode (UTF-16 LE)` 來呈現字元，如下範例： ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -w ``` 2. 原生模式 (`-n`) (native mode) 透過 bcp 匯出資料，若選擇「原生模式」的話，匯出的資料會採用 SQL Server 的原生資料格式來儲存，所以匯出的資料檔將會是二進制的版本(binary format)，你將無法使用文字編輯器開啟檔案。不過，這種匯出方法最大的優點就是不用擔心資料欄位中有「分隔符號」的問題，因此匯入資料時也不會造成資料錯亂。 ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -n ``` 從「最佳實務」的角度出發，建議匯出資料盡量採用**原生模式**！ 瞭解這兩種匯出方式後，那要如何選擇呢？以下幾點可供各位參考： 1. 若你從 SQL Server 匯出的資料，最終是要匯入 SQL Server 的，那就用**原生模式** 2. 若你從 SQL Server 匯出的資料，最終是要匯入到其他類型的資料庫，那就用**字元模式** 3. 如果你要匯出資料給其他應用程式使用，可以考慮使用 `-t` 參數指定**欄位分隔符號**，例如 `-t","` 就是用逗號當作欄位分隔符號，這樣就可以直接用 Excel 開啟檔案了。也可以考慮使用 `-r` 參數指定**資料列分隔符號**，例如 `-r"0x0A"` (`0x0A` 是 `\n` 的 Hex 表示法) 就是用 `LF` 當成換行符號當作資料列分隔符號。 4. 如果你還是害怕資料庫中出現了跟**欄位分隔符號**或**資料列分隔符號**重複的字元，你可以用很長的字元當作分隔符號，例如 `-t"|||"` 或 `-r"0x0A0A0A"`，這樣就可以確保不會有重複的字元了。 ### 匯入資料 匯入資料的方式跟匯出資料的方式很像，參數的使用方式也差不多，就只有把 `OUT` 改成 `IN` 就是匯入資料了，我們可以來比較一下命令： - 匯出資料 ```powershell bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 ``` - 匯入資料 ```powershell bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 ``` 如果你希望匯入資料時想維持 IDENTITY 欄位(自動編號)的內容，可以加上 `-E` 參數，如下範例： ```powershell bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -E ``` 你比較要注意的地方就是，你是怎樣用 `bcp` 匯出的，就記得把用 `bcp` 匯出的參數記下來，匯入的時候用一樣的參數就可以正確匯入了。 匯入資料的時候，可以透過 `-h` 定義**查詢提示**([Query Hints](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686))，例如你希望匯入資料的過程中，不要讓其他使用者存取資料表，可以這樣下**鎖定資料表**指令： ```powershell bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -h"TABLOCK" ``` > 完整的查詢提示清單可參考 [Query Hints (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686#syntax) 文件。 透過 `bcp` 匯入資料時，預設是「所有資料」會進行一次性的批次匯入，匯入的過程中也會產生一次交易(Transaction)。但如果你想要控制匯入的「批次」數量，也可以加上 `-b` 參數，自行決定一次要處理幾筆資料。但這裡比較需要注意的地方是，每個批次都會產生一次交易，如果上個批次交易成功，這次批次卻有錯誤發生的話，上一個批次的匯入資料是不會 Rollback 的！ ```powershell bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -h"TABLOCK" -b5000 ``` 匯入資料時非常有可能因為匯入的資料格式錯誤，導致匯入失敗，這時候你可以透過 `-m` 參數，指定最多允許的錯誤筆數，預設是 `10` 筆，如果超過 10 筆錯誤，就會中斷匯入，並且產生錯誤記錄檔，記錄下錯誤的資料內容。但有時候我們希望「一筆資料都不能少」，此時你就可以加上 `-m1` 讓匯入過程中，只要有一筆資料錯誤，就中斷匯入。 ```powershell bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -h"TABLOCK" -m1 ``` ### 建立格式檔 (Format files) 由於在 SQL Server 之間搬移資料，透過 `bcp` 非常適合，但每次匯入資料時要先去找出 `bcp` 匯出時所用的參數就覺得有點不太可靠。因此，`bcp` 可以讓你定義匯入資料的**格式檔**(Format file)，讓 `bcp` 在匯入資料時，可以透過格式檔的定義來讀取資料。 BCP 的格式檔有兩種格式： - 純文字格式 ``` 14.0 4 1 SQLCHAR 0 12 "\t" 1 CourseID "" 2 SQLCHAR 0 100 "\t" 2 Title SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 12 "\t" 3 Credits "" 4 SQLCHAR 0 12 "\r\n" 4 DepartmentID "" ``` - XML 格式 ```xml ``` 這兩種格式都是「好讀」但「不好寫」，所以你可以透過 `bcp` 來產生格式檔，使用方式跟**匯出資料**有點像，如下範例： 1. 產生純文字的格式檔 (字元模式) ```batch bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_c.fmt" -S"(localdb)\MSSQLLocalDB" -T -c ``` 2. 產生純文字的格式檔 (原生模式) ```batch bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.fmt" -S"(localdb)\MSSQLLocalDB" -T -n ``` 3. 產生 XML 格式檔 (字元模式) ```batch bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_c.xml" -S"(localdb)\MSSQLLocalDB" -T -x -c ``` 4. 產生 XML 格式檔 (原生模式) ```batch bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.xml" -S"(localdb)\MSSQLLocalDB" -T -x -n ``` 以下是一個完整的練習情境： 1. 匯出資料 (原生模式) ```batch bcp "ContosoUniversity.dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -n ``` 2. 建立格式檔 (原生模式) ```batch bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.fmt" -S"(localdb)\MSSQLLocalDB" -T -n ``` 3. 匯入資料時指定格式檔 ```batch bcp "ContosoUniversity.dbo.Course" IN "G:\BCP\Course.bcp" -f"G:\BCP\Course_n.fmt" -S"(localdb)\MSSQLLocalDB" -T ``` ### 總結 在瞭解了上述 `bcp` 的介紹後，應該不難發現 `bcp` 的強大之處，尤其是**格式檔**的運用，更是可以讓各種異質資料整合時更加強大。 我簡單總結幾種情境： 1. 從 SQL Server 移轉資料到另一台 SQL Server 這種情境應該是最簡單的，透過**原生模式**的匯出，搭配**格式檔**的定義，匯出匯入資料將會非常安全！ 2. 從 SQL Server 移轉資料到另一種關聯式資料庫或應用程式 匯出資料時只要搭配 `-c` 使用**字元模式**輸出，搭配 `-C` 指定字集，再用自訂的 `-t` 與 `-r` 輸出，就可以輕易的交由另一個應用程式匯入或使用資料。 3. 從另一種關聯式資料庫或應用程式匯入資料到 SQL Server 只要先確認輸入的格式，搭配自訂的格式檔，就可以輕易的匯入資料。不過自訂格式檔有一點點小門檻，第一次寫比較容易寫錯，只要多測試個幾次應該都能順利匯入資料。 ### 相關連結 - [Bulk Import and Export of Data (SQL Server) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/import-export/bulk-import-and-export-of-data-sql-server?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) - [bcp utility - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686) - [BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16&WT.mc_id=DT-MVP-4015686)