3

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

Sébastien Sevrin
  • 5,267
  • 2
  • 22
  • 39
Rakesh
  • 313
  • 1
  • 3
  • 14

1 Answers1

5

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);
}
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Can I do it using table value variable .if yes can you give me Example as per case defined in question.. How I can pass table type variable using dapper n insert data in multiple relational tables – Rakesh May 01 '15 at 16:42
  • @Rakesh yes, dapper can use TVPs - but you need to a: define a custom type on the server, b: populate your data into a `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 together – Marc Gravell May 01 '15 at 17:10
  • Thanks for your reply. But if you can provide some sample code about bulk insert in multiple tables as cade define in question using TVP parameter in store proc than it will be gr8 help for me. – Rakesh May 01 '15 at 20:03
  • @Rakesh `conn.Execute(SQL, new { param = table });` - or perhaps more usefully: http://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-valued-parameters – Marc Gravell May 01 '15 at 23:29
  • Thanks a lot but I need insert identity I'd of first table in second table .how I can insert that using tvp in case of bulk insert – Rakesh May 02 '15 at 06:19
  • 1
    @Rakesh via the "with output" clause – Marc Gravell May 02 '15 at 07:06
  • Marc: Thanks a lot for your help. If possible for you than is it possible to provide sample code as I am new for TVP for bulk insert.. – Rakesh May 02 '15 at 10:10
  • I had similar issue @Rakesh. Since `WITH OUTPUT` won't necessarily guarantee order, I think you have 2 options: you can either make the first table have a non-IDENTITY primary key so you can generate the id in c# ahead of time. Or you can hackily store a `TempID` on your first table and join that accordingly when inserting. Probably makes sense to go the former, I would think. But if anyone else knows a slicker way, I'm all ears. – JohnnyFun Apr 11 '21 at 05:04
  • Actually, you can use a `MERGE`/`OUTPUT` statement instead of an INSERT statement and get access to the `IDENTITY` and Source tvp's "fake" value to create a map table. Just make your `ON` clause `1=0` and only handle `WHEN NOT MATCHED BY TARGET THEN INSERT...` – JohnnyFun Jun 02 '21 at 18:52