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("...");
oConn.Open();
using var oTrans = oConn.BeginTransaction();
try
{ //寫入大量資料到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,這就會變成:
- 除非你能保證TableOnAp.iAutoIndex內的值都是唯一且不與TableOnSql.iAutoIndex,不然丟上去一定錯(違反PK條件約束)。
- 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名稱");
- 插入大數量資料另外有TVP(Table Value Parameter)的模式可使用,但資料庫限制是Microsoft SQL Server 2008以上,若有興趣可以參考黑大的這篇文章:SQL 2008 TVP資料匯入之火力展示。