SQL筆記:快速寫入大量資料到資料庫中的方法

最近有一個需求是需要在極短的時間內對Microsoft SQL Server中的某張資料表,一次寫入超過一萬筆資料,一開始是想要偷懶使用Dapper回傳的IEnumerable的集合物件直接Insert回寫,結果當然慘不忍睹,Dapper忠實地把你的資料列舉出來一筆筆翻譯成SQL INSERT指令回傳,一萬多筆寫了1x秒還不一定寫得完,後來發現這樣不行勉強包上交易(Transaction)才把時間拉回7秒左右。

七秒在網頁介面上可是一個不小的等候,上網查詢發現只有安裝需要付費的Dapper Plus才可以辦到Bulk Insert,當下馬上放棄改回經典的ADO.NET來運行。

利用ADO.NET的BulkCopy來進行大量資料寫入

經典的System.Data.SqlClient.SqlBulkCopy早就幫你寫好類別方法,快快樂樂的免費使用即可,只要準備好DataTable就可以順利的寫入,一萬筆資料目測不到一秒就完成,超簡單、超直覺、超暴力。

Step 1. 假設在SQL Server上要被插入資料的資料表,其名稱為TableOnSql,資料結構如下:

iAutoIndex int (PK; AUTO INCREMENT)
cName nvarchar(10)

Step 2. 假設在AP端已經有一張DataTable資料表,其名稱為TableOnAp,資料結構如下:

cNameOthers nvarchar(10)

Step 3. BulkCopy(Bulk Insert)根本不會在乎你的DataTable(TableOnAp)裡面的欄位名稱是什麼,他在乎的是欄位的順序與型別,只要跟TableOnSql資料表的順序與型別錯誤就會馬上報錯,因此以例子中的TableOnAp.cNameOthers很顯然是要對應到TableOnSql.cName,只要是順序對一切就會正確,只可惜在TableOnSql.cName的前端還有一個自動增量的PK名為iAutoIndex擋住,這個我們會在程式碼中解決掉。

using var oConn = new System.Data.SqlClient.SqlConnection("...");
using var oTrans = oConn.BeginTransaction();
try
{
  oConn.Open();
  //寫入大量資料到SQLServer
  using var oBulk = new System.Data.SqlClient.SqlBulkCopy(oConn, System.Data.SqlClient.SqlBulkCopyOptions.Default, oTrans);
  oBulk.DestinationTableName ="TableOnSql";

  //為了符合SQL端的TableOnAp資料表,所以硬插入一個新欄位名為iAutoIndex並將其移到第一欄
  TableOnAp.Columns.Add("iAutoIndex", typeof(Int32)).SetOrdinal(0);

  //測試用途:模擬針對欄位寫入資料
  //foreach (System.Data.DataRow oRow in TableOnAp.Rows) {
  //  oRow["iAutoIndex"] = 123;
  //}

  //寫入到資料庫
  oBulk.WriteToServer(TableOnAp);

  oTrans.Commit();
}
catch (Exception oEx)
{
  oTrans.Rollback();
  throw new System.Exception($"寫入資料庫失敗並回滾:{oEx.Message}");
}

SqlBulkCopyOptions.KeepIdentity的意思

官方針對SqlBulkCopyOptions.KeepIdentity的中文解釋是:「保留來源識別值。 如果未指定,則識別值依目的地指派。」,以這篇文章的例子就是「如果TableOnAp.iAutoIndex有數值的話,那就用TableOnAp.iAutoIndex當作值;若TableOnAp.iAutoIndex沒有指定值那就以TableOnSql.iAutoIndex的資料定義為準」。

然而尷尬的是TableOnSql.iAutoIndex是主要鍵值(PK;PRIMARY KEY),PK在伺服器端通常都會被定義成自動增量且不能為null,這就會變成:

  1. 除非你能保證TableOnAp.iAutoIndex內的值都是唯一且不與TableOnSql.iAutoIndex,不然丟上去一定錯(違反PK條件約束)。
  2. TableOnAp.iAutoIndex就算沒有設定值就直接丟上去也會報錯(DBNull)。

SqlBulkCopyOptions.KeepIdentity動作分析表

DataTable之自動增量欄位:有值且為單一值 DataTable之自動增量欄位:NULL
SqlBulkCopyOptions.KeepIdentity 違反 PRIMARY KEY 條件約束 'TableOnSql'。無法在物件 'TableOnSql' 中插入重複的索引鍵。陳述式已經結束。 資料行 'iAutoIndex' 不允許 DBNull.Value。
SqlBulkCopyOptions.Default 成功;欄位值依照資料庫設定進行自動增量。 成功;欄位值依照資料庫設定進行自動增量。

結論

因此若有PK主要索引鍵的需求,我的建議一律設定成SqlBulkCopyOptions.Default,這樣一來就算TableOnAp.iAutoIndex內的值有被汙染也不需要特別在乎(處理或清洗)。

備註

如果不想要用欄位的「順序」來進行SqlBulkCopy的話,也可以使用欄位的「名稱」來進行對應,詳見:SqlBulkCopyColumnMappingCollection.Add,例如:oBulk.ColumnMappings.Add("TableOnAp端的iAutoIndex名稱", "TableOnSql端的iAutoIndex名稱");

SQL MSSQL BulkInsert BulkCopy Dapper DapperPlus ADO.NET