I want to batch insert a large amount of generated data which has a circular dependency (a column in each table is foreign key constrained to the other table). To get around this, I want to just turn off the foreign key constraints, insert the data, and then turn the constraints back on.
Googling around, I found a bunch of solutions, but none of them worked. Right now I have:
ALTER TABLE TableName NOCHECK CONSTRAINT ALL
The command runs and doesn't produce any errors, but when I attempt to clear the table in preparation for inserting the data, I get the following error:
System.Data:0:in `OnError': The DELETE statement conflicted with the REFERENCE constraint "FK_1_2_ConstraintName". The conflict occurred in database "DatabaseName", table "dbo.SomeOtherTable", column 'PrimaryKey'.\r\nThe statement has been terminated.\r\nChecking identity information: current identity value '0', current column value '0'.\r\nDBCC execution completed. If DBCC printed error messages, contact your system administrator. (System::Data::SqlClient::SqlException)
My current theory is that this is caused by a foreign key constraint on the other table which depends on the table being changed.
There are two solutions I can come up with to this problem:
Go through all the tables with dependencies on the table I'm inserting into and disable their foreign key constraints. This seems unnecessarily complicated.
Disable foreign key constraints on all of the tables in the database.
Either solution would work, but I'm not sure where to start on either solution. Any ideas?