How to Efficiently Insert Large Datasets from a List using SqlBulkCopy?

Bulk Data Insertion from List<> using SqlBulkCopy

Question: How to efficiently insert a large number of records from a List<> of simple objects using SqlBulkCopy?

Answer:

To optimize bulk insertion, consider utilizing FastMember library. This approach avoids the overhead of DataTable by directly streaming the data from the object list. Here's how it's done:

using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

ObjectReader

ObjectReader allows you to work with non-generic data sources and dynamically determine member names. However, specifying the member names upfront enhances performance. Additionally, you can leverage SqlBulkCopy's ColumnMappings feature to customize column mapping.

The above is the detailed content of How to Efficiently Insert Large Datasets from a List using SqlBulkCopy?. For more information, please follow other related articles on the PHP Chinese website!