您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SqlBulkCopy 大量資料遷移實戰：從入門到調優

編輯：關於SqlServer

ASP.NET 2.0 引入的 SqlBulkCopy 類，是 .NET 開發者進行高效率資料大量載入的利器。它繞過普通 INSERT 的解析、編譯、執行計畫產生等開銷，直接以資料流形式將 DataTable、DataReader、陣列批次寫入 SQL Server，效能較逐筆寫入提升 10 倍至 100 倍。本文系統梳理核心 API、關鍵屬性、實戰範本與避坑指南。

核心寫入方法對比

方法	適用場景	效能特徵
`WriteToServer(DataReader)`	海量資料、單向流式讀取、記憶體敏感環境	最快，不佔用客戶端大量記憶體，流式傳輸
`WriteToServer(DataTable)`	中等資料量、需在記憶體中轉換/清洗資料	需先載入完整 DataTable，記憶體壓力較大
`WriteToServer(DataRow[])`	批次更新、需篩選特定狀態列	靈活但需自行管理批次大小

五大關鍵屬性精解

BatchSize (預設 0 = 全部一次送): 建議設定 1000~5000。過大導致交易日誌暴漲、鎖升級；過小增加網路往返。配合目標表索引策略調整：無索引堆積表可設大一點；重索引表建議較小批次。
BulkCopyTimeout (預設 30 秒): 大批次務必拉長（如 300~3600 秒）。超時會觸發全量回滾，已寫入資料全丟失。
ColumnMappings: 來源與目標欄位名稱/順序不一致時必須顯式映射。忽略此屬性會導致資料錯位或轉型失敗。
NotifyAfter + SqlRowsCopied 事件: 長時間作業的進度回報、心跳監控、取消權杖觸發點。
DestinationTableName: 需包含 Schema（如 dbo.TargetTable），避免解析歧義。

實戰範本：DataReader 流式遷移 + 進度回報

using (var srcConn = new SqlConnection(srcConnStr))
using (var destConn = new SqlConnection(destConnStr))
{
    srcConn.Open();
    destConn.Open();

    using (var cmd = new SqlCommand("SELECT Id, Name, Amount FROM SourceTable", srcConn))
    using (var reader = cmd.ExecuteReader())
    using (var bulk = new SqlBulkCopy(destConn))
    {
        bulk.DestinationTableName = "dbo.DestTable";
        bulk.BatchSize = 2000;
        bulk.BulkCopyTimeout = 1800;
        bulk.NotifyAfter = 5000;
        bulk.SqlRowsCopied += (s, e) => 
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss}] 已寫入 {e.RowsCopied:N0} 筆");

        // 明確欄位映射（來源序數 → 目標名稱）
        bulk.ColumnMappings.Add("Id", "Id");
        bulk.ColumnMappings.Add("Name", "FullName");
        bulk.ColumnMappings.Add("Amount", "TotalAmount");

        bulk.WriteToServer(reader);
    }
}

避坑清單

觸發器與約束： 預設會觸發 AFTER INSERT Trigger、檢查 FK/CHECK。大量載入前建議 ALTER TABLE ... NOCHECK CONSTRAINT ALL / DISABLE TRIGGER ALL，完成後重啟並 CHECK CONSTRAINT ALL 驗證。
身分識別欄位： 目標表有 Identity 欄位時，來源資料不可包含該欄，或使用 SqlBulkCopyOptions.KeepIdentity 保留來源值。
並行度控制： 同一目標表並行多個 BulkCopy 易發生鎖升級甚至死鎖。建議單寫入器 + 多讀取器管線，或按分區鍵拆分並行。
資料類型轉換： SqlBulkCopy 依賴 ADO.NET Type Mapper，特殊類型（sql_variant, UDT, XML Schema Collection）需預先在客戶端轉為支援類型。