I need to import millions of records in multiple sql server relational 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 I can do bulk insert of collection of millions of records using dapper in one single Insert statement
Dapper just wraps raw ADO.NET; raw ADO.NET doesn't offer a facility for this, therefore dapper does not. What you want is SqlBulkCopy. You could also use a table-valued-parameter, but this really feels like a SqlBulkCopy job.
In a pinch, you can use dapper here - Execute will unroll an IEnumerable<T> into a series of commands about T - but it will be lots of commands; and unless you explicitly enable async-pipelining, it will suffer from latency per-command (the pipelined mode avoids this, but it will still be n commands). But SqlBulkCopy will be much more efficient.
If the input data is an IEnumerable<T>, you might want to use ObjectReader from FastMember; for example:
IEnumerable<SomeType> data = ...
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
bcp.DestinationTableName = "SomeTable";
bcp.WriteToServer(reader);
}
DataTable of the right shape, and c: pass that DataTable in as a parameter to dapper, remembering to call dataTable.SetTypeName("YourCustomType") so that ADO.NET can connect things togetherconn.Execute(SQL, new { param = table }); - or perhaps more usefully: stackoverflow.com/questions/6232978/…