数万件のデータをforeachで1件ずつINSERTしていたら激遅でTimeOut。こういうときはSqlBulkCopyを使えば、DataTableをまとめてドンと入れられて、速度が桁違いになる。
基本の使い方
用意したDataTableを、そのままWriteToServerで流し込むだけ。DataTableの列名と、登録先テーブルの列名を合わせておくのがコツ。
using Microsoft.Data.SqlClient; // 旧: using System.Data.SqlClient;
using System.Data;
// dt は登録したいデータが入ったDataTable
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var bulk = new SqlBulkCopy(conn))
{
bulk.DestinationTableName = "dbo.SHOHIN"; // 登録先テーブル
bulk.WriteToServer(dt); // まとめてINSERT
}
}※「Microsoft.Data.SqlClient」は旧「System.Data.SqlClient」の 名前空間の新しいバージョンです。
「Microsoft.Data.SqlClient」が見つからない場合は「Nuget パッケージの管理」を開いて 「Microsoft.Data.SqlClient」をインストールしてください。
列名をマッピングする
DataTableとテーブルで列の順番が違ったり名前が一部違ったりする場合は、ColumnMappingsで対応付ける。指定しておくと順番に依存せず安全。
using (var bulk = new SqlBulkCopy(conn))
{
bulk.DestinationTableName = "dbo.SHOHIN";
// DataTableの列名 → テーブルの列名 で対応付け
bulk.ColumnMappings.Add("HINMEI", "PRODUCT_NAME");
bulk.ColumnMappings.Add("KINGAKU", "PRICE");
bulk.WriteToServer(dt);
}コピペで使える汎用メソッド
接続文字列・テーブル名・DataTableを渡すだけでBulkINSERTする形にしておくと使い回せる。列名が一致している前提で自動マッピングしている。
using Microsoft.Data.SqlClient;
using System.Data;
/// <summary>
/// DataTableの内容をSqlBulkCopyでまとめてINSERTする
/// </summary>
public static void BulkInsert(string connectionString, string tableName, DataTable dt)
{
if (dt == null || dt.Rows.Count == 0) return;
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var bulk = new SqlBulkCopy(conn))
{
bulk.DestinationTableName = tableName;
bulk.BatchSize = 1000; // 1000件ずつ送る
bulk.BulkCopyTimeout = 60; // タイムアウト（秒）
// DataTableの列名で自動マッピング（順序に依存しない）
foreach (DataColumn col in dt.Columns)
{
bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulk.WriteToServer(dt);
}
}
}注意点
列の型を合わせるのは必須（DataTable側とテーブル側で型が違うとエラー）。BatchSizeは大きすぎるとメモリを食い、小さすぎると遅くなるので1000〜5000あたりが無難。
途中で失敗したら全部なかったことにしたい場合は、SqlTransactionを渡してSqlBulkCopyをトランザクション内で実行する。IDENTITY列を明示的に入れたい場合はSqlBulkCopyOptions.KeepIdentityを指定。
トランザクションを使う場合
単純に「BulkInsertだけを全件成功/全件失敗にしたい」場合は、SqlBulkCopyOptions.UseInternalTransactionを指定するだけでOK。SqlBulkCopyが内部でトランザクションを自動生成してくれる。
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulk.DestinationTableName = tableName;
// ...（以下同じ）
}BulkInsertの前後に他のSQL処理も含めて1つのトランザクションにまとめたい場合（例：先に対象データをDELETEしてからBulkInsertする、など）は、SqlTransactionを明示的に生成し、SqlBulkCopyのコンストラクタに渡す。
using (var transaction = conn.BeginTransaction())
{
try
{
// 例: 事前にDELETEなど
using (var cmd = new SqlCommand("DELETE FROM ...", conn, transaction))
cmd.ExecuteNonQuery();
// SqlBulkCopyの第二引数を「SqlBulkCopyOptions.Default」にし、第三引数にトランザクションを渡す
using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
{
bulk.DestinationTableName = tableName;
// ...（以下同じ）
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}※UseInternalTransactionと外部のSqlTransactionは同時に使えない（排他）。またBatchSizeを指定していると、外部トランザクションなしの場合はバッチごとに部分コミットされてしまうため、「全件成功か全件失敗か」を厳密に保証したいときは外部トランザクションを使うかBatchSizeを外すこと。
さいごに
1件ずつINSERTと比べて、早さは段違い。大量データの登録が遅いと感じたら、まずSqlBulkCopyを検討する価値あり。
前に紹介したDataTableの分割・変換と組み合わせれば、「加工してからまとめて登録」まで一気通貫でできる。
コメント