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
I am wondering if there is a solution for this?
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
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.
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
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.