SqlBulkCopy for two tables with one-to-many association

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

Question

I've employedSqlBulkCopy in my earlier software and have benefited from its speed-of-light advantageINSERTS . However, I was just adding data into one table at the time.

I currently have two tables that are connected one-to-many, i.e.A table has a foreign keyB Thus, each entry inB carries an insert in that results in a idA .

Do you know whether there is a solution to this problem?

Example:

I'll use a better illustration of this and pray that we finally come up with a workable answer.

The name of our table is Contacts. Additionally, because each contact may have one or more email addresses, we will keep track of them in an other database named ContactEmails. SoContacts.Id on ContactEmails, become FK (sayContactEmails.ContactId ).

Consider inserting 1000 Contacts, each of which has zero or more Emails. Of course, we want to utilizeSqlBulkCopy the two tables.

The issue is that we are only aware of a new Contact's information when we input it.Id Once the Contact is added, we can verify that it was properlyId is e.g. 15. Therefore, we add three emails to this contact, and each of them will haveContactEmails.ContactId the number 15. But prior to the contact being added to the database, we are unaware of 15.

All contacts may be bulk-inserted into the table. However, the link is broken when it comes to their email since emails are unaware of their own contacts.

1
0
8/27/2015 1:03:11 PM

Popular Answer

Before bulk inserting, disable the restrictions (foreign key). Then turn it back on.

Verify that there are no breaches of referential integrity.

The following command will make the FK and CHECK restrictions inactive:

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

If I understand you well, you should be able to deactivate unique restrictions but not primary keys.

0
8/27/2015 9:55:35 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow