萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server BCP 工具完整指南:高效匯出匯入資料至文字檔

`bcp` (Bulk Copy Program) 是 SQL Server 內建的高效能命令列工具,專為大量資料在資料庫與平面檔案間高速移動設計。支援原生格式、字元格式、Unicode、XML 等多種格式,是 DBA 與開發人員進行資料遷移、備份、餵測試資料的必備技能。

一、 核心語法架構

bcp {[[database_name.][schema.]]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [options...]

方向參數說明

參數方向用途
in檔案 → 資料表/檢視表大量匯入
out資料表/檢視表 → 檔案大量匯出
queryout查詢結果 → 檔案自訂查詢匯出 (必須用雙引號包裹查詢)
format建立格式化檔產生 `.fmt` 檔供後續匯入匯出複用

二、 關鍵格式化選項 (必選其一)

選項資料格式特性適用場景
-nNative (原生)保留資料型別、二進位、高效能、不跨平台SQL Server 之間移轉
-cCharacter (字元)Tab 分欄、換行分列、可讀、跨平台匯出給 Excel、文字編輯器、異構系統
-wWide Char (Unicode)NCHAR 儲存、Tab/換行分隔、支援多國語言含中文/特殊字元、異構系統
-NNative + Unicode 字元非字元欄位用 Native,字元欄位用 Unicode,效能佳SQL Server 間移轉且含擴展字元
-f format_file格式化檔精確控制每欄型別、長度、終止符、對應順序複雜結構、欄位順序不一、自動化腳本

三、 常用控制參數速查表

參數功能預設/備註
-S server[\instance]指定目標伺服器/實例本機預設實例可省略
-U login / -P passwordSQL 驗證登入-P 留空會提示輸入;-T 為信任連接 (Windows 驗證,推薦)
-t field_term欄位終止符預設 `\t` (Tab);CSV 常用 `,`
-r row_term列終止符預設 `\n` (換行);Windows 常用 `\r\n`
-F first_row / -L last_row匯入/匯出資料列範圍1-based;-L 0 表示最後一列
-b batch_size批次大小每批次一個交易;預設全量一批。大量匯入建議 1000-5000
-m max_errors最大錯誤容忍筆數預設 10;達到即中止
-e err_file錯誤資料導向檔案記錄無法匯入的列,方便事後修正
-q啟用 QUOTED_IDENTIFIER物件名含空格/保留字時必須,物件名用雙引號包裹
-k保留 NULL 值匯入時不將空字串轉為預設值
-E保留身分識別欄位值匯入時使用檔案中的 IDENTITY 值而非自動產生
-h "hints"大量匯入提示如 `TABLOCK` (鎖定整張表提升速度)、`ROWS_PER_BATCH`、`ORDER`
-a packet_size網路封包大小4096-65535 bytes;大量作業調大可提升吞吐

四、 實戰範例集錦

1. 匯出資料表為 Tab 分隔文字檔 (最常用)

bcp AdventureWorks2019.dbo.Customers out C:\Export\Customers.txt -c -T -S localhost

2. 匯出查詢結果為 CSV (含標題列)

-- 步驟 1: 匯出標題
bcp "SELECT 'CustomerID,Name,Email,CreateDate'" queryout C:\Export\Customers.csv -c -t, -T -S localhost

-- 步驟 2: 匯出資料 (追加模式需手動合併或用腳本)
bcp "SELECT CustomerID, Name, Email, CreateDate FROM AdventureWorks2019.dbo.Customers WHERE IsActive=1" queryout C:\Export\Customers_Data.csv -c -t, -T -S localhost
-- 最後用 copy /b 或 PowerShell 合併兩檔

3. 高效匯入大量資料 (搭配 TABLOCK、批次)

bcp AdventureWorks2019.dbo.StagingTable in C:\Data\BigData.txt -c -t, -b 5000 -h "TABLOCK" -T -S localhost -e C:\Log\ImportErrors.log

4. 建立並使用格式化檔 (自動化匯入關鍵)

-- 互動式建立 fmt 檔
bcp AdventureWorks2019.dbo.TargetTable format nul -c -f C:\Fmt\TargetTable.fmt -T -S localhost
-- 過程中會詢問每欄前綴長度、型別、終止符,完成後產生 .fmt 檔

-- 後續自動化匯入直接引用 fmt
bcp AdventureWorks2019.dbo.TargetTable in C:\Data\Data.txt -f C:\Fmt\TargetTable.fmt -T -S localhost

五、 進階議題與避坑指南

1. 編碼與代碼頁 (-C)

  • `-c`/`-w` 模式下,`-C` 指定來源檔案編碼:`RAW` (無轉換,最快)、`ACP` (ANSI 1252)、`OEM` (預設)、`65001` (UTF-8)。
  • UTF-8 檔案建議使用 `-w` (Unicode) 或 `-C 65001` 搭配 `-c`。

2. 日期時間格式陷阱

  • 匯出 `datetime`/`smalldatetime` 時,`bcp` **永遠輸出 ODBC 格式 (`YYYY-MM-DD HH:MM:SS[.mmm]`)**,不受 `-V` 或 `-6` 影響。
  • 匯入舊版相容格式需預處理檔案或使用格式化檔指定轉換。

3. `bcp` vs `BULK INSERT` vs `OPENROWSET` vs SSIS

4. 權限問題排查

  • 使用 `-T` (信任連接) 時,執行 `bcp` 的 Windows 帳戶需具備 SQL Server 登入權限及目標資料庫權限。
  • 匯出檔案路徑為 **Client 端路徑**;匯入檔案路徑若用 `BULK INSERT` 則為 **Server 端路徑**。

結語

`bcp` 以輕量、高速、無相依性著稱,是 SQL Server 資料搬移的「瑞士刀」。掌握 `-c/-w/-n 格式選擇、-t/-r 分隔符自訂、-b 批次控制、-f 格式化檔複用、-h TABLOCK 加速` 這五大核心技巧,即可應付 90% 的生產環境大量資料匯入匯出需求。

copyright © 萬盛學電腦網 all rights reserved
工具執行位置權限需求適用場景
bcpClient (命令列)檔案系統讀寫權限 + DB 權限自動化腳本、跨伺服器、無 SSIS 環境
BULK INSERTServer (T-SQL)`ADMINISTER BULK OPERATIONS` / `BULKADMIN` 角色 + 檔案路徑伺服器可見存儲程序內嵌、排程作業、無 Client 環境
OPENROWSET(BULK...)Server (T-SQL)同 BULK INSERT臨時查詢、資料轉換管線中繼
SSISClient/ServerSSIS 目錄權限複雜 ETL、資料清洗、流程控制、視覺化開發