2 ways to run BULK INSERT

When there are lots of data to insert, we cannot insert one by one. There are several ways to bulk insert, and here are 3 examples for that!

SqlBulkCopy from DataTable

Create a DataTable and specify which database table to insert.

We can specify BatchSize so that we do not push too much pressure on memory. It will chunk data into specified batch size to reduce memory usage but affect its performance.

Example

  1. Create DataTable

2. Execute SqlBulkCopy

BulkInsert from EFCore.BulkExtension

This is an extension for Entity Framework Core. If you are using EntityFramework and do not spend time on creating DataTable and SqlBulkCopy, this is an easy way out!

Since it uses SqlBulkCopy underneath, it works much faster than SaveChanges after AddRange().

https://github.com/borisdj/EFCore.BulkExtensions

Performance Comparison

EFCore’s BulkInsert is pretty easy, but it is slower than SqlBulkCopy. There were about 2 seconds difference to insert 20,000 records.

However, EFCore.BulkExtension works pretty good and Entity Framework can save tons of development time. If this performance difference is not going to be an issue, Entity Framework is still a great way to go.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store