How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

I was given this solution over at SQLTeam.com:

Use:

 EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Then import your data

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Using that method I was able to import all the data in with no issues.

I produced an exact copy of a database on my machine from a server which I did not control.

I'm a schmuck, but this is what I did:

1. Created the DB from my script that was in source control (hint, hint!) If you don't have the script, you can always generate it from the existing DB through the Tasks option.

2. If any data was auto-inserted into YourDB at creation, run a DELETE FROM YourDB.dbo.tblYourTable.

   • You can't truncate data when foreign keys exist so you have to use DELETE.

3. Run this on your destination server: USE YourDB; EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all';

4. Right click on YourDB in Object Explorer. Click Tasks -> Import Data...

5. The first few screens of the wizard are self explanatory.

6. On the Select Source Table and Views screen of the wizard, click the checkbox next to every table you want copied.

7. For each row (table) on that screen, click on it so it is highlighted and then click Edit Mappings.

8. For each row (table), click/check Append rows to the destination table and Enable identity insert.

   • If you click Delete rows in destination table it will fail because it doesn't issue a DELETE command, it issues a TRUNCATE command which still conflicts with our foreign keys because TRUNCATE is not governed by the NOCHECK CONSTRAINT from earlier.

9. Click through the rest of the wizard and click Finish.

10. Watch for errors; warnings are probably ok to ignore.

    • If there are errors, click the Report button and view the report. Try and suss out what was a Success, Error, and Stopped. You'll probably need to fix whatever was the root cause of the error which is buried in that report somewhere. Then, you'll probably need to do a DELETE FROM YourDB.dbo.theErrorTable. Now click the back button on the import wizard and uncheck every table that was a Success. Repeat ad infinitum.

11. Run this on your destination server: USE YourDB; EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all';

    • If there are errors, ... I don't know, but fix them and try again!

12. Yay! :)

Thank you to everybody answering this question and questions similar to this on the SE network for helping me figure this out.

In the import wizard, you can delete the rows first and if you have identity fields, then you can enable identity insert on as below

If you want to disable check constraint, then when the wizard asks you to save the package, save it and then edit the connection manager as below :

Note: You cannot TRUNCATE the table when there are Foreign Keys defined.