SQL Server 海量資料匯入最快方案：BULK INSERT 實戰與效能調優

面對 200 萬筆以上資料匯入需求，逐筆 INSERT 耗時數小時不可接受。BCP 雖快但為命令列工具，整合性差。BULK INSERT 結合高速度與 T-SQL 可程式性，測試 100 萬筆匯入僅需 20 秒內，是關聯式資料庫內建最強方案。本文詳細說明語法、參數、日誌模式切換技巧與實戰注意事項。

核心語法範例

-- 1. 切換至大量記錄復原模式，減少記錄檔膨脹
ALTER DATABASE Taxi SET RECOVERY BULK_LOGGED;

-- 2. 執行大量匯入
BULK INSERT Taxi..Detail
FROM 'E:\Out.txt'
WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
);

-- 3. 還原完整復原模式
ALTER DATABASE Taxi SET RECOVERY FULL;

關鍵參數解析

三大缺點與對策

1. 需獨佔資料表：TABLOCK 會阻塞其他存取。→ 離峰時段執行，或先匯入暫存表再 SWITCH 分區。
2. 產生大量交易記錄：即使 BULK_LOGGED 模式仍記錄配頁資訊。→ 結合 TABLOCK 達到最小化記錄；若資料庫為簡單復原模式則自動最小化。
3. 來源檔案格式限制：需固定分隔符、編碼一致。→ 先用 ETL 工具（SSIS、Python）預處理轉標準 CSV/TSV。

進階控制：逐列驗證與錯誤導向

若需逐列驗證（如 FK 檢核、業務規則），可：

• 先 BULK INSERT 至暫存表（HEAP 無索引最快）。
• 用 MERGE 或 INSERT...SELECT 搭配 WHERE NOT EXISTS 寫入正式表，錯誤導向錯誤表。
• 犧牲少量速度換取資料品質與可追蹤性。

效能基準參考（單表 100 萬筆，10 欄位，SSD、32GB RAM）

最佳實務檢核清單

• ☐ 來源檔案編碼與 DATAFILETYPE 一致
• ☐ 目標表無非叢集索引（匯入後再建）
• ☐ 目標表為 HEAP 或叢集索引鍵與匯入順序一致
• ☐ 已設定 TABLOCK 並切換復原模式
• ☐ 磁碟 I/O 子系統無瓶頸（建議資料與記錄檔分離不同磁碟）