ADO - Bulk Data Insertion Using SqlBulkCopy

Bulk data insertion is the process of inserting a large number of records into a database in a single operation rather than executing multiple individual INSERT statements. In ADO.NET, the SqlBulkCopy class provides a fast and efficient way to transfer large datasets into Microsoft SQL Server. It is especially useful when importing data from CSV files, Excel spreadsheets, XML files, or another database.

Traditional insertion methods execute one SQL command for each record. While this approach works well for small datasets, it becomes slow and resource-intensive when handling thousands or millions of rows. SqlBulkCopy significantly improves performance by sending data to SQL Server in batches, reducing network traffic and minimizing database overhead.

What is SqlBulkCopy?

SqlBulkCopy is a class in the System.Data.SqlClient namespace that enables high-speed data transfer into SQL Server tables. Instead of inserting one row at a time, it streams multiple rows directly into the destination table. This method is commonly used in enterprise applications, data migration projects, ETL (Extract, Transform, Load) processes, and reporting systems.

The class works with several data sources, including:

  • DataTable

  • DataSet

  • IDataReader

  • DataRow arrays

It can insert data into an existing SQL Server table with minimal coding.

Why Use SqlBulkCopy?

Bulk insertion offers several advantages over standard insert operations.

Improved Performance

Since all records are transmitted together, SQL Server processes them much faster than individual insert commands.

Reduced Network Overhead

Instead of sending one database request per record, bulk copy sends data in large batches, reducing communication between the application and the database server.

Efficient Memory Usage

When used with a DataReader, data can be streamed directly without loading the entire dataset into memory.

Ideal for Large Datasets

Applications dealing with thousands or millions of records benefit significantly from bulk copy operations.

Basic Working Process

The bulk insertion process generally involves the following steps:

  1. Create a SQL Server connection.

  2. Prepare the source data.

  3. Create a SqlBulkCopy object.

  4. Specify the destination table.

  5. Map source columns to destination columns.

  6. Execute the bulk copy operation.

  7. Close the database connection.

Example Table

Suppose SQL Server contains the following table:

CREATE TABLE Employees
(
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

Creating a DataTable

DataTable dt = new DataTable();

dt.Columns.Add("EmployeeID", typeof(int));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Columns.Add("Department", typeof(string));
dt.Columns.Add("Salary", typeof(decimal));

dt.Rows.Add(101, "Rahul", "HR", 45000);
dt.Rows.Add(102, "Anita", "Finance", 52000);
dt.Rows.Add(103, "Vikram", "IT", 60000);

The DataTable acts as the source for bulk insertion.

Performing Bulk Copy

using(SqlConnection con = new SqlConnection(connectionString))
{
    con.Open();

    SqlBulkCopy bulkCopy = new SqlBulkCopy(con);

    bulkCopy.DestinationTableName = "Employees";

    bulkCopy.WriteToServer(dt);

    con.Close();
}

This code inserts all rows from the DataTable into the Employees table in one operation.

Column Mapping

Sometimes the source and destination column names differ. In such cases, column mapping is required.

Example:

Source Table:

EmpID Name Dept Income

Destination Table:

EmployeeID EmployeeName Department Salary

Column mapping can be defined as follows:

bulkCopy.ColumnMappings.Add("EmpID", "EmployeeID");
bulkCopy.ColumnMappings.Add("Name", "EmployeeName");
bulkCopy.ColumnMappings.Add("Dept", "Department");
bulkCopy.ColumnMappings.Add("Income", "Salary");

This ensures that each source column is copied into the correct destination column.

Using Batch Size

Instead of inserting all rows at once, data can be divided into batches.

bulkCopy.BatchSize = 1000;

If there are 10,000 records, SQL Server processes them in ten batches of 1,000 records each.

Benefits include:

  • Lower memory consumption

  • Better transaction management

  • Easier recovery if an error occurs

Setting Bulk Copy Timeout

Large datasets may require additional processing time.

bulkCopy.BulkCopyTimeout = 120;

This allows the operation to continue for up to 120 seconds before timing out.

Keeping Identity Values

Suppose the destination table contains an identity column.

Normally SQL Server generates new identity values automatically.

Example:

EmployeeID INT IDENTITY(1,1)

If existing identity values should be preserved, use:

SqlBulkCopy bulkCopy =
new SqlBulkCopy(connection,
SqlBulkCopyOptions.KeepIdentity, null);

This inserts the original identity values instead of generating new ones.

Using Transactions

Transactions ensure that either all records are inserted successfully or none are inserted if an error occurs.

Example:

SqlTransaction transaction = con.BeginTransaction();

SqlBulkCopy bulkCopy =
new SqlBulkCopy(con,
SqlBulkCopyOptions.Default,
transaction);

try
{
    bulkCopy.WriteToServer(dt);

    transaction.Commit();
}
catch
{
    transaction.Rollback();
}

This maintains database consistency during bulk operations.

Reading Data from Another Database

SqlBulkCopy is often used to transfer data between databases.

Example:

SqlCommand cmd = new SqlCommand("SELECT * FROM OldEmployees", sourceConnection);

SqlDataReader reader = cmd.ExecuteReader();

SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection);

bulkCopy.DestinationTableName = "Employees";

bulkCopy.WriteToServer(reader);

This method streams data directly from the source database to the destination database without loading everything into memory.

Performance Comparison

Method Speed Suitable For
Individual INSERT Statements Slow Small datasets
ExecuteNonQuery() in Loop Moderate Medium-sized datasets
SqlBulkCopy Very Fast Large datasets

For example:

  • 500 records: Little difference in speed.

  • 50,000 records: SqlBulkCopy is significantly faster.

  • 500,000 records: Bulk copy greatly outperforms traditional insertion methods.

Common SqlBulkCopy Properties

Property Purpose
DestinationTableName Specifies the destination SQL Server table.
BatchSize Defines how many rows are processed in each batch.
BulkCopyTimeout Sets the maximum execution time.
ColumnMappings Maps source columns to destination columns.
NotifyAfter Triggers an event after a specified number of rows are copied.

Common Methods

Method Description
WriteToServer(DataTable) Copies all rows from a DataTable.
WriteToServer(DataReader) Copies rows from a DataReader.
WriteToServer(DataRow[]) Copies selected rows from a DataRow array.

Best Practices

  • Use SqlDataReader instead of DataTable for very large datasets to reduce memory usage.

  • Use transactions to maintain data integrity during bulk operations.

  • Set an appropriate BatchSize for better performance and error handling.

  • Configure BulkCopyTimeout when importing large volumes of data.

  • Define column mappings explicitly when source and destination schemas differ.

  • Validate and clean data before starting the bulk copy process.

  • Close database connections promptly after the operation completes.

  • Test bulk insertion with realistic data volumes to optimize performance.

Advantages

  • Extremely fast data insertion.

  • Efficient use of network and database resources.

  • Supports multiple data sources.

  • Reduces application execution time.

  • Handles millions of records efficiently.

  • Supports transactions and batch processing.

  • Ideal for ETL, migration, synchronization, and data import tasks.

Limitations

  • Works only with Microsoft SQL Server.

  • Primarily designed for insert operations; it does not update or delete existing records.

  • Destination table must already exist.

  • Source and destination data types should be compatible.

  • Poorly configured batch sizes or transactions can impact performance.

Applications of SqlBulkCopy

SqlBulkCopy is widely used in scenarios such as importing customer records from CSV files, migrating data between databases, synchronizing information from external systems, loading data into data warehouses, processing payroll or financial records, importing student admissions or examination data, generating business intelligence reports, and handling high-volume data uploads in enterprise applications.

Conclusion

SqlBulkCopy is one of the most efficient features of ADO.NET for inserting large amounts of data into SQL Server. By transferring records in batches and minimizing database communication, it offers substantial performance improvements over traditional insertion techniques. Features such as column mapping, transactions, identity preservation, and configurable batch processing make it a reliable choice for enterprise applications that require fast, scalable, and secure bulk data loading.