1

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.

disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • possible duplicate of [How does SqlBulkCopy circumnavigate foreign key constraints?](http://stackoverflow.com/questions/22092091/how-does-sqlbulkcopy-circumnavigate-foreign-key-constraints) – Cyril Durand Aug 27 '15 at 10:00
  • @CyrilDurand the question does not work for me. My problem is that every row in `A` can have one or more child rows in `B`. But before we run the INSERT for `A` we do not know what the row id will be so that `B` will get the same. The row in `B` will only know its parent's id when its parent has been inserted. Isn't it right? So we can do a bulk insert for records in `A` but when it comes to be, they don't know their parents. – disasterkid Aug 27 '15 at 12:23
  • @CyrilDurand I updated the question with more information just in case. – disasterkid Aug 27 '15 at 13:04

1 Answers1

0

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.

Abhay Chauhan
  • 404
  • 3
  • 11
  • The problem is, unless we insert a record in `A`, we do not know its id. So if there is a record in `B` it will not know it belongs to `A` unless its parent has already been created. So I don't think removing the constraints will solve the problem. – disasterkid Aug 27 '15 at 12:15
  • It is true, disabling constraints will not help in this case. Add more information in your question on how are you trying to do this. What have you tried so far and where is it you are getting an error or stuck. This will hep understand your problem. – Abhay Chauhan Aug 27 '15 at 12:28
  • OK I will try to add more info. – disasterkid Aug 27 '15 at 12:31
  • I tried to elaborate the problem a bit further. Would appreciate any help. – disasterkid Aug 27 '15 at 13:03
  • Me too have same question however not getting the solution, seems will have to end up doing bulk copy for parent first and then bulk copy for child table :( – Girish Sakhare Dec 15 '15 at 05:25