SQL Server 海量資料高速導入：BULK INSERT 實戰與大量記錄模式切換

痛點：百萬級 INSERT 慢如蝸牛

逐筆 `INSERT` 或小批次參數化雖安全，但百萬筆動輒數小時。`BCP` 命令列雖快但難整合自動化流程。BULK INSERT 是 T-SQL 原生、可編程、高效能的最佳平衡點：100 萬筆 < 20 秒 (視硬體/網路/索引而定)。

核心語法與關鍵選項

BULK INSERT [Schema].[TableName]
FROM 'E:\Data\Import.txt'          -- 檔案路徑 (伺服器端本地或 UNC 路徑)
WITH (
    DATAFILETYPE = 'char',          -- 'char' | 'native' | 'widechar' | 'widenative'
    FIELDTERMINATOR = ',',          -- 欄位分隔符
    ROWTERMINATOR = '\n',           -- 列分隔符 (Windows: '\r\n', Linux: '\n')
    FIRSTROW = 2,                   -- 跳過標題列
    TABLOCK,                        -- 關鍵：取得表級鎖 (Sch-M)，允許最小記錄，大幅提速
    CHECK_CONSTRAINTS,              -- 檢查 CHECK/FOREIGN KEY (預設關閉，開啟會降速)
    FIRE_TRIGGERS,                  -- 觸發 INSERT TRIGGER (預設關閉)
    MAXERRORS = 0,                  -- 容錯筆數 (0 即失敗即停)
    ERRORFILE = 'E:\Data\Err.log'   -- 錯誤行導出檔
);

三大缺點與應對策略

殺手鐧：動態切換大量記錄模式 (最小化記錄)

完整恢復模式下 `BULK INSERT` 仍會完整記錄每筆資料 (除非滿足最小記錄條件：`TABLOCK` + 空表/新頁面 + 無觸發器/無 FK/無計算欄位)。最穩妥：導入前切 `BULK_LOGGED`，導入後切回 `FULL` 並立即備份記錄。

-- 1. 切換至大量記錄模式 (元資料操作，極快)
ALTER DATABASE [TargetDB] SET RECOVERY BULK_LOGGED;
GO

-- 2. 執行大量導入 (配合 TABLOCK 觸發最小記錄)
BULK INSERT [TargetDB].[dbo].[DetailTable]
FROM 'E:\Data\Import.txt'
WITH (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK,               -- 必須！鎖表換取最小記錄
    FIRSTROW = 2
);
GO

-- 3. 立即切回完整模式
ALTER DATABASE [TargetDB] SET RECOVERY FULL;
GO

-- 4. 關鍵：立即進行一次記錄備份，接上新的記錄鏈，確保可還原性
BACKUP LOG [TargetDB] TO DISK = 'E:\Backup\TargetDB_Log_AfterBulk.trn';
GO

進階：格式化檔案應對複雜結構

當來源檔欄位順序、數量、型別與目標表不一致，或含引號/逸出字元時，使用 XML 格式化檔 (`*.fmt`)。

-- 1. 產生範本格式化檔
BCP [TargetDB].[dbo].[DetailTable] FORMAT nul -c -t, -r\n -f Detail.fmt -T -S.

-- 2. 編輯 Detail.fmt (XML 格式)，調整 SOURCE 欄位對應、長度、終止符、COLLATION 等

-- 3. 使用格式化檔導入
BULK INSERT [TargetDB].[dbo].[DetailTable]
FROM 'E:\Data\Import.txt'
WITH (FORMATFILE = 'E:\Data\Detail.fmt', TABLOCK);

替代方案對比

生產環境檢查清單

• [ ] 目標表無觸發器 / FK / 計算欄位 (或暫時禁用 `ALTER TABLE NOCHECK CONSTRAINT ALL`)。
• [ ] 目標表為 Heap 或 Clustered Index 且為空/新分區 (最小記錄最易觸發)。
• [ ] 磁碟空間預留：資料檔增長 + 記錄檔 (即使最小記錄也需配置頁面) + TempDB。
• [ ] `MAXERRORS` + `ERRORFILE` 設定妥當，失敗可追蹤。
• [ ] 導入後 `UPDATE STATISTICS` + `ALTER INDEX REBUILD` (若大量導入導致統計過時/碎片)。
• [ ] **務必** 導入後切回 `FULL` 並立即 `BACKUP LOG`。