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
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";
bcp.WriteToServer(reader);
}
source to share