
ADO.NET SqlBulkCopy class lets you insert large volumes of data into SQL Server quickly and efficiently. Instead of running one INSERT statement per row, it sends records to the database in batches, which greatly reduces round trips and processing time. This makes it the preferred choice for data imports, migrations, and ETL tasks in ASP.NET Core applications. The speed gain can be dramatic. Depending on your hardware, network, table design, and batch size, SqlBulkCopy can load 1 million records in just a few seconds, while inserting them row by row can take many minutes. In this tutorial, you will learn how to use SqlBulkCopy to bulk insert records into a SQL Server table.
The SqlBulkCopy has a method called WriteToServer() that takes a SqlDataReader object, and through this method the bulk inserts are done quickly.
Suppose there is a database table called AccountData which has 1 million records, and we want to copy all it’s records to another table called Account. Then we use SqlBulkCopy class.
The definition of AccountData and Account tables are given below:
CREATE TABLE [dbo].[AccountData] (
[Id] INT IDENTITY (1, 1) NOT NULL Primary key,
[PersonName] VARCHAR (50) NOT NULL,
[TotalCash] MONEY NOT NULL
);
CREATE TABLE [dbo].[Account] (
[Id] INT IDENTITY (1, 1) NOT NULL Primary key,
[Name] VARCHAR (50) NOT NULL,
[Money] MONEY NOT NULL
);
Go to the Controller to add TransferData actions whose code is given below:
public IActionResult TransferData()
{
return View();
}
[HttpPost]
[ActionName("TransferData")]
public IActionResult TransferData_Post()
{
string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
SqlConnection connection = new SqlConnection(connectionString);
string sql = $"Select * From AccountData";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
// create a SqlBulkCopy object
SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Account";
//Mappings
sqlBulk.ColumnMappings.Add("PersonName", "Name");
sqlBulk.ColumnMappings.Add("TotalCash", "Money");
//Copy rows to destination table
sqlBulk.WriteToServer(dataReader);
return View();
}
You can see that first we got a SqlDataReader object for reading the data from the AccountData table:
SqlDataReader dataReader = command.ExecuteReader();
Next, we create SqlBulkCopy object by passing the SqlConnection object to it:
SqlBulkCopy sqlBulk = new SqlBulkCopy(connection);
To tell SqlBulkCopy object how the data should be copied, we need to provide the destination table name like:
sqlBulk.DestinationTableName = "Account";
We also have to provide the column mappings, by using the ColumnMappings.Add() method. This method takes 2 parameters:
This mappings code is:
sqlBulk.ColumnMappings.Add("PersonName", "Name");
sqlBulk.ColumnMappings.Add("TotalCash", "Money");
Finally, we use the WriteToServer() method to start the copy operation, like:
sqlBulk.WriteToServer(dataReader);
Create the View called TransferData in the Views ➤ Home folder and add the following code to it:
<h1>Transfer data from SqlBulkCopy class</h1>
<form method="post">
<button type="submit" class="btn btn-sm btn-primary">Transfer Data</button>
</form>
When the Transfer Data button is clicked then the TransferData action will be called and the SqlBulkCopy code will execute.
Add some records to the AccountData table and then run your project and go to the URL – /Home/TransferData. Click the Transfer Data button. Now open the Account table and you will see new records from the AccountData are copied to it.
Can we use ADO.NET and Entity Framework Core together? Yes, we can use them together in ASP.NET core application since Entity Framework Core is an ORM that is made on top of ADO.NET. ADO.NET is easy to learn and very flexible to create and manage codes.
You can download the source code using the below link:
In this tutorial you learned to use the SqlBulkCopy class of ADO.NET to insert large volumes of records into a SQL Server table quickly and efficiently in ASP.NET Core. Instead of running one INSERT statement per row, SqlBulkCopy sends data to the database in batches, which cuts round trips and saves a lot of time. Use it whenever your next project needs to import, migrate, or load a large number of records.
SqlBulkCopy is the fastest built-in ADO.NET option for inserting many rows into SQL Server.DestinationTableName to choose the target table, and use ColumnMappings when source and destination column names differ.BatchSize and BulkCopyTimeout for very large loads to balance speed and memory use.using statement so the connection and SqlBulkCopy object are always disposed, and wrap the call in try-catch to handle SqlException.What is SqlBulkCopy in ADO.NET?
SqlBulkCopy is a class in Microsoft.Data.SqlClient that copies large amounts of data into a SQL Server table in bulk, much faster than inserting rows one at a time.
When should I use SqlBulkCopy instead of regular INSERT statements?
Use it when you need to load thousands or millions of rows, such as during data imports, migrations, or ETL jobs. For a few records, a normal parameterized INSERT is simpler.
How do I insert records with SqlBulkCopy? – Fill a DataTable with your data, open a SqlConnection, create a SqlBulkCopy object, set DestinationTableName, and call WriteToServer().