16

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.

27

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.

5

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.

  • You're a life saver – Tom Gullen Mar 16 '16 at 7:57
  • 1
    Seriously thank you so much, had a backup on a corrupted drive, couldn't copy the DB anywhere (i/o error). Importing the data a few tables at a time this way helped me recover 99% of it. – Tom Gullen Mar 16 '16 at 8:08
  • 1
    You rock! Another command that helped me: EXEC sp_msforeachtable 'delete from ? '; If an error happens delete everything is the law. sp_msforeachtable script is here gist.githubusercontent.com/metaskills/893599/raw/… – Sanchitos Nov 23 '18 at 5:02
4

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.

3

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.

  • 3
    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
1

Problems like this show us that the people who make SQL server have never actually used their product. This is such a glaring omission that one has to wonder what else they simply forgot to do correctly (I've got a list of about 30 other maddening issues like this that I've had to overcome to make this work as other DBs do out of the box; including the fact that we need a lousy wizard to do this in the first place [if I had the time I've spent waiting for this wizard to connect and enumerate the same tables for the same DB, every time, back...I'd have time for a nice vacation]).

I'm very lazy and don't want to type EXEC sp_msforeachtable ... twice every time I do this. My work around has been to leave the constraints on the production server and remove them from the dev server. This will prevent the error but this method does have a few VERY BIG side effects. First, you will no longer be able to just restore a full backup to your dev server (unless you're OK with removing them all again). Second, this works best when you are sure that the consumers of your data also enforce these constraints (or do not care about them). In my case, we only have one consumer (our website) so we've built these constraints into the site code as well (ie before we delete a user record we delete all phone records for that user first). Yes, this essentially negates the need for constraints in the first place and doubles the work that I need to do but it also gives me a chance to verify that my code works with or without DBMS based constraints (the fact is that they are still on the prod server only as a contingency plan). You could call this a flaw in my design but I'd rather call it a workaround for a flawed DBMS. At any rate, it's still quicker and easier to do this anywhere else than from within MSSQL because it is unable to cope with it's own design.

0

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.

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

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

  • 1
    Ignore the primary key would help with foreign keys? – dezso Feb 9 '15 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 '15 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy