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.
- 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().
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.