SQL Server BCP 批量複製工具六大陷阱與避坑指南

BCP 簡介

Bulk Copy Program (BCP) 是 SQL Server 命令列匯入匯出大量資料的核心工具，方便但有限制。

六大陷阱

1. 不支援 UTF-8 編碼

• SQL Server 內部用 UTF-16 (雙位元組 Unicode，NVARCHAR)
• BCP 僅支援：原生格式、字元格式 (-c，ANSI/Code Page 1252)、Unicode 格式 (-w，UTF-16)
• 解法：來源資料轉為 UTF-16 (含 BOM) 或 ANSI，再用 BCP 匯入

2. 匯出順序不保證 (無 ORDER BY)

• 查詢無 ORDER BY 時，順序視同隨機 (依索引/掃描順序)
• 匯入順序若影響正確性 (如 FK 依賴、觸發器邏輯)，必須在查詢加 ORDER BY

3. 帶參數預存程序無法直接用於 BCP 查詢

• BCP 使用 SET FMTONLY ON 分析結果集結構
• 動態 SQL/參數化 SP 無法正確分析 → 編譯失敗
• 解法：
  • 建立無參數包裝 SP，內部讀取資料來源取得參數
  • 改用 sqlcmd
  • OPENROWSET 技巧 (有限制，慎用)

4. 匯入時來源/目標欄定義必須完全一致

• 包含 NULL/NOT NULL、IDENTITY、COLLATE、DEFAULT
• 目標表缺少定義 → 靜默資料損壞 (截斷、預設值不同、NULL 違規)
• 建議：匯入前用 BCP ... FORMAT 產生格式檔，對照驗證

5. 預設不觸發目標表觸發器

• 大量匯入若觸發器會極度混亂、效能崩潰
• 需加參數 -h "FIRE_TRIGGERS" 啟用
• 副作用：每批次觸發一次、使用 tempdb 儲存列版本、大量資料可能耗盡 tempdb

6. 無法追加輸出到現有檔案

• BCP 匯出檔案必須為新建，不支援 Append
• 解法：分多檔匯出 → COPY export1.dat + export2.dat export.dat 合併

最佳實務檢查清單