逐筆 INSERT 會產生大量網路往返與交易日誌寫入，效能極差。.NET Framework 2.0 引入 SqlBulkCopy，可將 DataTable、DataReader、DataRow[] 以資料流方式高速灌入 SQL Server，速度比傳統方式快數十倍。
-- 目標表 (Tempdb)
USE TEMPDB;
CREATE TABLE TEMP_ORDERS (
TEMP_ORDERID INT,
TEMP_CUSTOMERID NCHAR(5),
TEMP_ORDERDATE DATETIME,
TEMP_SHIPNAME NVARCHAR(40)
);
protected void Page_Load(object sender, EventArgs e)
{
// 1. 來源資料：從 Northwind.Orders 讀入 DataTable
DataTable dtSrc = new DataTable();
using (var connSrc = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True"))
using (var adapter = new SqlDataAdapter(
"SELECT ORDERID, CUSTOMERID, ORDERDATE, SHIPNAME FROM ORDERS", connSrc))
{
adapter.Fill(dtSrc);
}
// 2. 目的地連線 + 交易
using (var connDest = new SqlConnection("Data Source=.;Initial Catalog=Tempdb;Integrated Security=True"))
{
connDest.Open();
using (var tran = connDest.BeginTransaction())
{
using (var bulk = new SqlBulkCopy(connDest, SqlBulkCopyOptions.Default, tran))
{
bulk.DestinationTableName = "TEMP_ORDERS";
bulk.BulkCopyTimeout = 1000; // 秒
bulk.NotifyAfter = 10; // 每 10 列觸發事件
bulk.SqlRowsCopied += OnRowsCopied; // 進度回呼
// 欄位對應 (來源→目標)
bulk.ColumnMappings.Add("ORDERID", "TEMP_ORDERID");
bulk.ColumnMappings.Add("CUSTOMERID", "TEMP_CUSTOMERID");
bulk.ColumnMappings.Add("ORDERDATE", "TEMP_ORDERDATE");
bulk.ColumnMappings.Add("SHIPNAME", "TEMP_SHIPNAME");
try
{
bulk.WriteToServer(dtSrc);
tran.Commit();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally { dtSrc = null; }
}
}
}
}
private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Response.Write($"已複製：{e.RowsCopied}
");
}
| 屬性/方法 | 說明 |
|---|---|
ColumnMappings | 來源與目標欄位名稱不同時必須明確對應，否則依序位置綁定易出錯 |
BatchSize | 每批次筆數，預設全量；大資料量建議 5000~10000，平衡記憶體與日誌 |
BulkCopyTimeout | 整體作業逾時秒數，預設 30 秒，大量資料需調大 |
NotifyAfter + SqlRowsCopied | 進度回呼，適合長時間作業的 UI 回報 |
WriteToServer 多載 | 支援 DataTable、IDataReader、DataRow[]，IDataReader 記憶體佔用最低 |
SQL Profiler 顯示實際執行：
insert bulk TEMP_ORDERS
([TEMP_ORDERID] Int, [TEMP_CUSTOMERID] NChar(5) COLLATE Chinese_PRC_CI_AS,
[TEMP_ORDERDATE] DateTime, [TEMP_SHIPNAME] NVarChar(40) COLLATE Chinese_PRC_CI_AS)
核心優勢：單一連線、單一交易、最小化日誌 (視復原模式)、避免逐筆 RPC 往返。
生產環境建議：
1. 來源端用 SqlDataReader 串流讀取，避免 DataTable 佔用雙倍記憶體。
2. 目標表匯入前 卸除非叢集索引、匯入後重建，大幅加速。
3. 使用 SqlBulkCopyOptions.TableLock 取得大量更新鎖，減少鎖升級開銷。
4. 監控 sys.dm_exec_requests 觀察 INSERT BULK 進度與等待類型。
