The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何使用 bcp 對 SQL Server 資料表進行批次匯出與匯入

📅 2023/09/17 11:12 📁 SQL Server

要匯入大量資料到資料庫中，其實有很多方法，其中一個方法則是透過 bcp 工具程式 (bulk copy program)，不過這個命令列工具跟一般 CLI 工具的參數用法有很大的不同，所以這篇文章我就來介紹一下如何使用 bcp 來匯出與匯入 SQL Server 中的資料。

bcp

基本語法

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]

由於參數用法很多，容易眼花撩亂，以下我就盡量用「範例」來解說各種用法。

基本語法範例

我以一個簡單的「匯出資料」範例開始，如下範例：

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 參數指定資料庫名稱：

bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c

如果想執行「任意 T-SQL 查詢」來匯出資料的話，也非常簡單，將 OUT 改成 QUERYOUT 輸出即可，如下範例：

bcp "SELECT [Title],[Credits] FROM [dbo].[Course]" QUERYOUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"
ContosoUniversity" -c

若要連接 Azure SQL Database 的話，由於不支援信任式連線，所以要改用帳號密碼連線，如下範例：

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)之間，並沒有用一個「空白字元」分隔喔！

如果想要留下匯出或匯入過程的記錄檔，也可以這樣下指令：

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) 輸出，這可能會在使用不同電腦匯入資料時遇到問題。

    bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c

    但你也可以用 -C (大寫的 C 喔) 指定字集，例如指定 65001 (UTF-8) 輸出，如下範例：

    bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C 65001

    也可以使用 -w 參數，指定使用 Unicode 字元模式，他預設會用 Unicode (UTF-16 LE) 來呈現字元，如下範例：

    bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -w

  2. 原生模式 (-n) (native mode)

    透過 bcp 匯出資料，若選擇「原生模式」的話，匯出的資料會採用 SQL Server 的原生資料格式來儲存，所以匯出的資料檔將會是二進制的版本(binary format)，你將無法使用文字編輯器開啟檔案。不過，這種匯出方法最大的優點就是不用擔心資料欄位中有「分隔符號」的問題，因此匯入資料時也不會造成資料錯亂。

    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 就是匯入資料了，我們可以來比較一下命令：

  • 匯出資料

    bcp "dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001

  • 匯入資料

    bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001

    如果你希望匯入資料時想維持 IDENTITY 欄位(自動編號)的內容，可以加上 -E 參數，如下範例：

    bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -E

你比較要注意的地方就是，你是怎樣用 bcp 匯出的，就記得把用 bcp 匯出的參數記下來，匯入的時候用一樣的參數就可以正確匯入了。

匯入資料的時候，可以透過 -h 定義查詢提示(Query Hints)，例如你希望匯入資料的過程中，不要讓其他使用者存取資料表，可以這樣下鎖定資料表指令：

bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -h"TABLOCK"

完整的查詢提示清單可參考 Query Hints (Transact-SQL) 文件。

透過 bcp 匯入資料時，預設是「所有資料」會進行一次性的批次匯入，匯入的過程中也會產生一次交易(Transaction)。但如果你想要控制匯入的「批次」數量，也可以加上 -b 參數，自行決定一次要處理幾筆資料。但這裡比較需要注意的地方是，每個批次都會產生一次交易，如果上個批次交易成功，這次批次卻有錯誤發生的話，上一個批次的匯入資料是不會 Rollback 的！

bcp "dbo.Course" IN "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -d"ContosoUniversity" -c -C65001 -h"TABLOCK" -b5000

匯入資料時非常有可能因為匯入的資料格式錯誤，導致匯入失敗，這時候你可以透過 -m 參數，指定最多允許的錯誤筆數，預設是 10 筆，如果超過 10 筆錯誤，就會中斷匯入，並且產生錯誤記錄檔，記錄下錯誤的資料內容。但有時候我們希望「一筆資料都不能少」，此時你就可以加上 -m1 讓匯入過程中，只要有一筆資料錯誤，就中斷匯入。

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 version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="CourseID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Credits" xsi:type="SQLINT"/>
  <COLUMN SOURCE="4" NAME="DepartmentID" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>

這兩種格式都是「好讀」但「不好寫」，所以你可以透過 bcp 來產生格式檔，使用方式跟匯出資料有點像，如下範例：

  1. 產生純文字的格式檔 (字元模式)

    bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_c.fmt" -S"(localdb)\MSSQLLocalDB" -T -c

  2. 產生純文字的格式檔 (原生模式)

    bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.fmt" -S"(localdb)\MSSQLLocalDB" -T -n

  3. 產生 XML 格式檔 (字元模式)

    bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_c.xml" -S"(localdb)\MSSQLLocalDB" -T -x -c

  4. 產生 XML 格式檔 (原生模式)

    bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.xml" -S"(localdb)\MSSQLLocalDB" -T -x -n

以下是一個完整的練習情境：

  1. 匯出資料 (原生模式)

    bcp "ContosoUniversity.dbo.Course" OUT "G:\BCP\Course.bcp" -S"(localdb)\MSSQLLocalDB" -T -n

  2. 建立格式檔 (原生模式)

    bcp "ContosoUniversity.dbo.Course" format nul -f "G:\BCP\Course_n.fmt" -S"(localdb)\MSSQLLocalDB" -T -n

  3. 匯入資料時指定格式檔

    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

    只要先確認輸入的格式，搭配自訂的格式檔，就可以輕易的匯入資料。不過自訂格式檔有一點點小門檻，第一次寫比較容易寫錯，只要多測試個幾次應該都能順利匯入資料。

相關連結

留言評論