Using SQLBulkCopy for multiple tables with references

c# sqlbulkcopy sql-server

Question

I am consuming a large amount of JSON data and storing that in a SQL Server database via the SQLBulkCopy class.

SqlBulkCopy bulkInsert = new SqlBulkCopy(conn);
bulkInsert.DestinationTableName = "dbo.Orders";
conn.Open();
bulkInsert.WriteToServer(rows.ToArray());

rows is a list of type DataRow.

The data I am consuming is orders, so the bulk of the data is being inserted into the orders table, but some other tables need to be inserted into as well, however these inserts must have a reference to the rows inserted into the orders table.

Is this possible with SQLBulkCopy or do I have to manually insert each row, query the database for the inserted row to get the reference field back and then use that value in future inserts?

Expert Answer

SqlBulkCopy cannot return the inserted identity value.

So using only SqlBulkCopy is impossible but it can be achieved by using some trick with a temporary table.

In short:

  • You bulk insert into a temporary table
  • You insert from temporary table to destination with the OUTPUT clause (to get identity value)
  • You set the identity value returned from the OUTPUT clause to every rows

Disclaimer: I'm the owner of the project Bulk Operations

This project overcome SqlBulkCopy limitation by adding some features like:

  • Outputting identity value
  • BulkInsert
  • BulkUpdate
  • BulkDelete
  • Bulk Merge

Example:

var bulk = new BulkOperation(connection)
bulk.DestinationTableName = "dbo.Orders";

bulk.ColumnMappings.Add("OrderId", ColumnMappingDirectionType.Output);
bulk.ColumnMappings.Add("Price");
bulk.ColumnMappings.Add("Quantity");

bulk.BulkInsert(rows);


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why