SqlBulkCopy for two tables with one-to-many association

bulkinsert c# one-to-many sqlbulkcopy sql-server

Question

I have used SqlBulkCopy in my previous program and have enjoyed the speed-of-light advantage of its INSERTS. But then, I was only inserting things in one table only.

I now have two tables with a one-to-many association i.e. table A has a foreign key in table B. So each record in B carries an id that is the result of an insert in A.

I am wondering if there is a solution for this?

Example:

I will give a better example on this and hope we find a good solution eventually.

We have a table called Contacts. And since each contact can have zero or more Email addresses we will store those emails in a separate table called ContactEmails. So Contacts.Id will become FK on ContactEmails (say ContactEmails.ContactId).

Let's say we would like to insert 1000 Contacts and each will have zero or more Emails. And we of course want to use SqlBulkCopy for both tables.

The problem is, it is only when we insert a new Contact that we know his/her Id. Once the Contact is inserted, we know the inserted Id is e.g. 15. So we insert 3 emails for this contact and all three will have ContactEmails.ContactId value of 15. But we have no knowledge of 15 before the contact is inserted into the database.

We can insert all contacts as bulk into the table. But when it comes to their email, the connection is lost because emails do not know their own contacts.

Popular Answer

Disable the constraints (foreign key) before bulk insert. Then enable it again.

Make sure you do not have referential integrity violations.

You can disable FK and CHECK constraints using below query:

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.



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