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

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.

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.

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.

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.

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

If you let the wizard to create the destination database it will handle everything on-the-fly.