Hello,

One of our developers is trying to do a bulk insert into multiple tables that are dependent on each other and some of the inserts are erroring out with foreign key constraint issue.

INSERT statement conflicted with the FOREIGN KEY constraint "FK_dependintable_table". The conflict occurred in database "database", table "dbo.table", column 'id'.

When I ran the profiler trace I saw the inserts running in multiple batches and I think that is the reason why its happening.

Is there a way that we can prevent this? I meant without dropping and adding constraints? Like at the scope of transaction?

Thanks a lot

• Edited by SQLSPUser Tuesday, August 25, 2015 6:14 PM

Would disabling the foreign key constraint be acceptable?

https://msdn.microsoft.com/en-us/library/ms175041.aspx

You would need to update the ETL load to disable the constraint prior to loading the tables and then enable them once all of them are fully loaded.

• Proposed as answer by Naomi NModerator Tuesday, August 25, 2015 6:22 PM

Personally, I'd load everything into Staging Tables, and then run a SProc to move the data from the Staging tables to your Production Tables.  Bulk Insert is kind of dumb, actually.  I've VERY FAST, but it's pretty limited in it's capabilities.

Please think about this. NO, if the data has DRI constraints, you have to have them or tell the customer that the data is total crap. 2+2=5 is always wrong!  Can you scrub the data before it get to the database?