Using SQLBulkCopy for multiple tables with references

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?

Jonathan Magnan · Accepted Answer · 2016-11-23 15:57:31Z

2

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);

answered Nov 23, 2016 at 15:57

Jonathan Magnan

11.4k2 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SQLBulkCopy for multiple tables with references

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related