Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY constraint" i have over 40 tables with lots of FK constraints, is there some easy way to deal with this without having to write a drop constraint/add constrat script?

Edit: I just found out that in Web edition of SQL Server, which is what I am running, DTS will not let you save packages.

share|improve this question

6 Answers 6

up vote 8 down vote accepted

I was given this solution over at


 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.

share|improve this answer
sp_msforeachtable (and sp_MSForEachDb) is undocumented and unsupported. You should not/avoid using it. It might skip the tables !! See this post from @AaronBertrand -->… and this connect item - (MS indicating that they wont fix it) -->… – Kin Jun 13 '13 at 16:02
Worked perfectly for me after hours of beating my brains out trying to get around the FK constraints for a data export. – levininja Mar 25 at 23:14

Don't drop the constraints.

What you should do is save the SSIS package that the wizard creates, then edit it in BIDS/SSDT. When you edit the package you'll be able to control the order that the tables are processed in so you can process the parent tables then process the child tables when all the parent tables are done.

share|improve this answer
That is not really effeciant either, it would take almost an hour right now to modify the package to make sure everything is in the correct order, and even then i can not be sure. This would get more cumbersom as the DB grows and to do it everytime, no thanks. There has to be a simple way of doing this. – user11512 May 26 '13 at 14:26

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

enter image description here

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 :

enter image description here

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

share|improve this answer

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.

share|improve this answer

I think you cannot perform backup and restore from production server as it is a crucial data. Well without the proper rights it really becomes more Complicated. But if you have db backup n restore right then you can perform it .

Or else, One way that I would recommend though is to drop all of your constraints and indexes and then again add them once the data has been imported or exported.

Not an exact answer but it will process fast.

share|improve this answer
Thanks, but I specificly said i dont want to script out a drop/create constraint script. – user11512 May 26 '13 at 14:27

Just read that topic. It is an old post but here is what I did to help future people reading this.

In my case I wanted to import to an empty identical table. When editing mapping, I select <ignore> for the primary key. All my content is being added automatically nicely.

Hope it helps someone

share|improve this answer
Ignore the primary key would help with foreign keys? – dezso Feb 9 at 8:12
In my case yes since I was actually duplicating a table. So I ended with the same primary keys. So foreign keys pointing to my table are still corresponding to the correct entry – Greg Feb 9 at 14:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.