Bulk insert into many relational tables using Dapper.

I need to import millions of records into multiple relational sql server tables.

TableA(Aid(pk),Name,Color)----return id using scope identity 
TableB(Bid,Aid(fk),Name)---Here we need to insert Aid(pk) which we got using scocpe Identity


How can I bulk insert a collection of millions of records using dapper in a single Insert declaration


source to share

1 answer

Dapper just wraps raw ADO.NET; raw ADO.NET does not offer the ability to do this, so dapper does not. You want SqlBulkCopy

. You can also use the table-valued-parameter parameter, but it does seem like an assignment SqlBulkCopy


As a last resort you can use dapper here - Execute

expands IEnumerable<T>

into a series of commands about T

- but there will be many commands; and unless you explicitly enable asynchronous pipelining, it will suffer from latency for each instruction (pipelined mode avoids this, but there will still be n instructions). But it SqlBulkCopy

will be much more effective.

If the inputs are IEnumerable<T>

, you can use ObjectReader

from FastMember; eg:

IEnumerable<SomeType> data = ...
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
    bcp.DestinationTableName = "SomeTable";




All Articles