SQL Server 大量資料處理兩大方案：SqlBulkCopy 批次插入與 SqlDataAdapter 批次更新完整範例

方案選擇指引

• 純大量插入：SqlBulkCopy (BCP) 效能最佳
• 混合插入/更新/刪除：SqlDataAdapter + UpdateBatchSize 效能優異

方案一：SqlBulkCopy 大量插入（含交易控制）

public static void BulkCopy(string connString, string tableName, DataTable dt)
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        using (SqlTransaction transaction = conn.BeginTransaction())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
            {
                bulkCopy.BatchSize = 2000;           // 每批 2000 筆
                bulkCopy.BulkCopyTimeout = 300;      // 逾時秒數
                bulkCopy.DestinationTableName = tableName;
                
                try
                {
                    // 欄位對應：DataTable 欄位名 = 資料表欄位名
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    
                    bulkCopy.WriteToServer(dt);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

關鍵參數說明

方案二：SqlDataAdapter 批次更新（支援 Insert/Update/Delete）

public static void Update(string connString, DataTable table)
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand comm = conn.CreateCommand();
        comm.CommandTimeout = 300;
        comm.CommandType = CommandType.Text;
        
        using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
        {
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
            commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            
            try
            {
                conn.Open();
                adapter.UpdateBatchSize = 5000; // 批次更新筆數
                
                // 開始交易
                adapter.SelectCommand.Transaction = conn.BeginTransaction();
                
                // 若 DataTable 有自訂 SelectCommand SQL
                if (table.ExtendedProperties["SQL"] != null)
                {
                    adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
                }
                
                adapter.Update(table); // 執行批次更新
                
                // 提交交易
                adapter.SelectCommand.Transaction.Commit();
            }
            catch (Exception ex)
            {
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }
                throw;
            }
        }
    }
}

關鍵技術點

1. UpdateBatchSize：啟用批次更新，減少往返次數（預設 1，逐筆執行）
2. SqlCommandBuilder：自動產生 INSERT/UPDATE/DELETE 指令，需資料表有主鍵
3. ConflictOption.OverwriteChanges：衝突時以資料庫最新值覆蓋
4. 交易控制：透過 SelectCommand.Transaction 統一管理
5. ExtendedProperties["SQL"]：可攜帶自訂查詢 SQL 供 CommandBuilder 參考

效能比較與適用場景

實務建議

1. 大量插入優先用 SqlBulkCopy，搭配交易確保一致性
2. 需更新/刪除時用 SqlDataAdapter，設定 UpdateBatchSize > 1
3. 資料量極大（百萬級）考慮分批次呼叫、暫存表、資料分割
4. 生產環境務必測試批次大小對效能與記憶體的影響
5. 避免在單一交易處理過多資料導致鎖定升級或記錄檔暴增