Bulk Upload into SQL Server using SqlBulkCopy and C#

Insert 100,000 rows with INSERT statements and you'll wait minutes; with SqlBulkCopy the same load takes under a second. It's the .NET API over the same bulk-load path that BCP and BULK INSERT use — minimal logging, batched network traffic, no per-row round trips. This post loads 100k rows from C#, measured for real, and covers the mapping, batching, and transaction details that separate a quick demo from a robust importer.

Why row-by-row INSERT is slow

Each INSERT is a network round trip, a parse, a plan, a log record, and a lock acquisition. At 100,000 rows even 2 ms per statement is over three minutes. Batching with table-valued parameters helps; SqlBulkCopy goes further by streaming rows through the TDS bulk-load protocol — the database writes pages, not statements.

The destination and the source

A fresh staging table (bulk loads belong in staging tables — validate there, then merge):

create table dbo.ImportedOrders (
    OrderId int not null, CustomerEmail nvarchar(200) not null,
    Amount decimal(10,2) not null, PlacedAt datetime2 not null);

The source here is a DataTable built in memory — in real life it's parsed from a CSV or an API feed:

var table = new DataTable();
table.Columns.Add("OrderId", typeof(int));
table.Columns.Add("CustomerEmail", typeof(string));
table.Columns.Add("Amount", typeof(decimal));
table.Columns.Add("PlacedAt", typeof(DateTime));
for (var i = 1; i <= 100_000; i++)
    table.Rows.Add(i, $"customer{i % 5000}@example.com", (i % 900) + 0.99m, baseDate.AddMinutes(i));

The bulk copy

var sw = Stopwatch.StartNew();
using (var bulk = new SqlBulkCopy(connection)
{
    DestinationTableName = "dbo.ImportedOrders",
    BatchSize = 10_000,
    BulkCopyTimeout = 120,
})
{
    bulk.ColumnMappings.Add("OrderId", "OrderId");
    bulk.ColumnMappings.Add("CustomerEmail", "CustomerEmail");
    bulk.ColumnMappings.Add("Amount", "Amount");
    bulk.ColumnMappings.Add("PlacedAt", "PlacedAt");
    await bulk.WriteToServerAsync(table);
}
sw.Stop();

Measured on a laptop against SQL Edge in Docker:

Bulk-inserted 100,000 rows in 807 ms

Under a second, database in a container, no tuning. The same data as individual inserts benchmarks in the minutes.

The options that matter

• Explicit ColumnMappings. Without them, SqlBulkCopy maps by ordinal — column order in your source silently must match the table. One reordered CSV column and your amounts land in the wrong field without an error. Always map by name.
• BatchSize — rows per batch sent to the server. 5k–20k is the useful range; 0 (everything in one batch) maximizes speed but means one giant transaction and no progress granularity.
• BulkCopyTimeout — the default 30 seconds will bite genuinely large loads; size it to your data.
• SqlBulkCopyOptions.TableLock — takes a bulk update lock, enabling parallel-free but faster loading into a heap; ideal for exclusive staging tables.
• EnableStreaming = true with an IDataReader source — for files bigger than memory, feed a reader (e.g. a CSV reader implementing IDataReader) so rows stream end to end without materializing a DataTable at all. The DataTable version here is fine up to a few hundred thousand rows.

Transactions: all or nothing

By default each batch commits separately — a failure mid-load leaves earlier batches in the table. For atomic imports, wrap the operation:

await using var tx = (SqlTransaction)await connection.BeginTransactionAsync();
using var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tx)
{ DestinationTableName = "dbo.ImportedOrders" };
// mappings + WriteToServerAsync...
await tx.CommitAsync();

With staging tables there's a simpler pattern: load into an empty staging table (truncate on failure and retry), then MERGE/INSERT...SELECT into the real table inside a normal transaction — validation SQL runs in the database where it's cheapest.

Constraints, triggers, and the fine print

Bulk load skips some things you may be relying on:

• Check constraints and triggers are NOT enforced/fired by default. Opt in with SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers — or, better, validate in staging.
• Identity columns: by default the server assigns new identities; KeepIdentity preserves source values (needed when importing related tables).
• NULLs vs defaults: KeepNulls controls whether a source NULL stays NULL or takes the column default.
• Errors report per batch, not per row — finding which row broke a constraint is another reason staging tables plus SQL validation beat loading straight into production tables.

When to reach for it

Anything above a few thousand rows on a regular basis: nightly imports, migration one-offs, event backfills, test-data generation. Below that, table-valued parameters or batched EF Core AddRange are simpler and fast enough. And if the source is another SQL Server table, plain INSERT...SELECT on the server beats hauling rows through your app entirely.

The runnable project — including the console SqlClient basics it builds on — is in the companion repository.