SqlBulkCopyを使う際に、(私が間抜けなばかりに)苦労したのでそのメモ書きである。
※本記事では、サンプルコードはC#のみとなる。
目次
・SqlBulkCopyとは何か、使う場面
・具体的な使い方
・注意点
・順番に関わらず、フィールド名を紐づける
・総括
SqlBulkCopyとは何か、使う場面
SqlBulkCopyとは、.Net Framework で使えるクラスで、
大量のデータを高速で一括insertできる。
例えば、郵便番号のデータなどは何十万件もある。
そういったデータをプログラム上で加工した上でDBに格納したい場合に楽になる。
insertする機能しかないのだが、ちょっと工夫すれば一括updateしたい場合にも活用できる。
一括insertではなく、一括updateをしたいのであれば、こちらの記事をおススメするので割愛する。
SQLServerで一時テーブルを使って更新を行う : 日曜ゲームクリエータの日記
SqlBulkCopyの具体的な使い方
本記事では、DataTableを用いて、単に一括insertする方法を紹介する
後の記事で、SqlBulkCopyとIDataReaderを併用した、メモリを節約する方法も紹介する予定だ。
using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { var dt = new DataTable(); dt.TableName = "test_table"; // テーブル名を設定 dt.Columns.Add("column1"); // テーブルのフィールドを設定 dt.Columns.Add("column2"); // テーブルのフィールドを設定 // データを1件追加 var dataRow = dt.NewRow(); dataRow["column1"] = "left1"; dataRow["column2"] = "right1"; dt.Rows.Add(dataRow); // データを2件目追加 dataRow = dt.NewRow(); dataRow["column1"] = "left2"; dataRow["column2"] = "right2"; dt.Rows.Add(dataRow); var connectionString = "接続文字列"; using (var bulkCopy = new SqlBulkCopy(connectionString)) { bulkCopy.DestinationTableName = dt.TableName; // テーブル名をSqlBulkCopyに教える bulkCopy.WriteToServer(dt); // bulkCopy実行 } } }
これで、「test_table」テーブルに、データが2件登録される。
column1 | column2 |
---|---|
left1 | right1 |
left2 | right2 |
注意点
DataTbaleに設定したフィールド名は無視され、設定した順番でマッピングされる。
~省略~ var dt = new DataTable(); dt.TableName = "test_table"; // テーブル名を設定 dt.Columns.Add("column2"); // 敢えてcolumn2を先に設定 dt.Columns.Add("column1"); // 敢えてcolumn1を後に設定 ~省略~
結果
column1 | column2 |
---|---|
right1 | left1 |
right2 | left2 |
注目してほしいのは、column1とcolumn2の値がDataTableで設定しているものとは逆になっていることだ。
順番に関わらず、フィールド名を紐づける
順番に関わらず、DataTableのフィールド名と実テーブルのフィールド名を紐づけるには、一手間加える必要がある。
~省略~ var connectionString = "接続文字列"; using (var bulkCopy = new SqlBulkCopy(connectionString)) { // フィールド名のマッピング foreach(var column in dt.Columns) { bulkCopy.ColumnMappings.Add(column.ToString(),column.ToString()); } bulkCopy.DestinationTableName = dt.TableName; // テーブル名をSqlBulkCopyに教える bulkCopy.WriteToServer(dt); } ~省略~
結果
column1 | column2 |
---|---|
left1 | right1 |
left2 | right2 |
〇総括
数万行以上となる大量のデータを挿入/更新する場合、1件ずつinsert/updateなどしていられない。
プログラム上で加工し、一括で挿入/更新するなら、SqlBulkCopyは私が思う最も速く簡単な方法だ。
ただフィールドの紐づけに関しては、順番を意識するか、明示的に設定する必要がある。