SqlBulkCopy

In simple words#

Learning objectives#

• Bulk insert from a DataTable
• Map columns explicitly
• Tune BatchSize and options
• Use a staging table for merges

Explanation#

SqlBulkCopy streams rows from a DataTable, DataRow[] or any IDataReader into a table.

Always add ColumnMappings: without them, columns are matched by position, which breaks when the table has an identity column or a different order.

BatchSize controls how many rows go per batch; SqlBulkCopyOptions.TableLock speeds up large loads; CheckConstraints and FireTriggers are off by default.

For “insert or update”, bulk load into a staging table, then run one MERGE or UPDATE/INSERT statement.

Code example: Import a CSV price list#

1var table = new DataTable();
2table.Columns.Add("Name", typeof(string));
3table.Columns.Add("Price", typeof(decimal));
4table.Columns.Add("Stock", typeof(int));
5

6foreach (var line in File.ReadLines("products.csv").Skip(1))   // Name,Price,Stock
7{
8    var parts = line.Split(',');
9    table.Rows.Add(parts[0], decimal.Parse(parts[1], CultureInfo.InvariantCulture), int.Parse(parts[2]));
10}
11

12await using var conn = new SqlConnection(cs);
13await conn.OpenAsync();
14using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.CheckConstraints, null)
15{
16    DestinationTableName = "dbo.Products",
17    BatchSize = 5000,
18    NotifyAfter = 20000,
19};
20bulk.ColumnMappings.Add("Name", "Name");
21bulk.ColumnMappings.Add("Price", "Price");
22bulk.ColumnMappings.Add("Stock", "Stock");
23bulk.SqlRowsCopied += (_, e) => Console.WriteLine(
quot;{e.RowsCopied:N0} rows…");
24

25var sw = Stopwatch.StartNew();
26await bulk.WriteToServerAsync(table);
27Console.WriteLine(
quot;Imported {table.Rows.Count:N0} rows in {sw.Elapsed.TotalSeconds:F1}s");

20,000 rows…
40,000 rows…
...
Imported 100,000 rows in 1.8s   (timings vary)

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. The CSV is parsed into a DataTable with the right types.
2. TableLock and CheckConstraints are enabled.
3. Explicit mappings skip the identity Id column.
4. SqlRowsCopied reports progress every 20,000 rows.

Real-world use case#

Common mistakes#

Best practices#

• Always map columns
• Load into a staging table for upserts
• Use an IDataReader source for huge files to avoid memory spikes
• Wrap in a transaction if the load must be all-or-nothing

Remember this#

Practice exercise#

1INSERT INTO dbo.Products (Name, Price, Stock)
2SELECT s.Name, s.Price, s.Stock
3FROM dbo.ProductsStaging s
4WHERE NOT EXISTS (SELECT 1 FROM dbo.Products p WHERE p.Name = s.Name);
5TRUNCATE TABLE dbo.ProductsStaging;

Mini quiz#

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

Related topics#

My notes#

Saved in this browser.