`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` 檔供後續匯入匯出複用 |
二、 關鍵格式化選項 (必選其一)
| 選項 | 資料格式 | 特性 | 適用場景 |
-n | Native (原生) | 保留資料型別、二進位、高效能、不跨平台 | SQL Server 之間移轉 |
-c | Character (字元) | Tab 分欄、換行分列、可讀、跨平台 | 匯出給 Excel、文字編輯器、異構系統 |
-w | Wide Char (Unicode) | NCHAR 儲存、Tab/換行分隔、支援多國語言 | 含中文/特殊字元、異構系統 |
-N | Native + Unicode 字元 | 非字元欄位用 Native,字元欄位用 Unicode,效能佳 | SQL Server 間移轉且含擴展字元 |
-f format_file | 格式化檔 | 精確控制每欄型別、長度、終止符、對應順序 | 複雜結構、欄位順序不一、自動化腳本 |
三、 常用控制參數速查表
| 參數 | 功能 | 預設/備註 |
-S server[\instance] | 指定目標伺服器/實例 | 本機預設實例可省略 |
-U login / -P password | SQL 驗證登入 | -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
| 工具 | 執行位置 | 權限需求 | 適用場景 |
bcp | Client (命令列) | 檔案系統讀寫權限 + DB 權限 | 自動化腳本、跨伺服器、無 SSIS 環境 |
BULK INSERT | Server (T-SQL) | `ADMINISTER BULK OPERATIONS` / `BULKADMIN` 角色 + 檔案路徑伺服器可見 | 存儲程序內嵌、排程作業、無 Client 環境 |
OPENROWSET(BULK...) | Server (T-SQL) | 同 BULK INSERT | 臨時查詢、資料轉換管線中繼 |
| SSIS | Client/Server | SSIS 目錄權限 | 複雜 ETL、資料清洗、流程控制、視覺化開發 |
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