CODEWITHSUNDEEP
sql-server-2008
A.Goutam
A.Goutam

Reputation: 3494

INSERT statement conflicted with the FOREIGN KEY constraint Import Data

I am importing data from one database to another database with same table structure using Task - Import Data . i always getting error INSERT statement conflicted with the FOREIGN KEY constraint . I want that when i import data i not get this error . import like this . Is this possible that i delete all the foreign key from the table and after import i run the script for foreign key to the table , if yes how can i do that . Thanks for your reply and comments

Upvotes: 1

Views: 606

Answers (1)

Dave C
Dave C

Reputation: 7392

I would suggest importing raw data into a staging table, which has no FK constraints. Then from there, you can quickly query the staging area, and determine which values are violating the FK constraints.

Ex. you create a table called tblImport, and the FK is on the field city. Your city table only has New York and Los Angeles, but when you select distinct city from tblImport you see Las Vegas is also there. Las Vegas is violating your constraint. You can then add Las Vegas to your city table, and import with out violating the FK constraint.

Once you have imported into the final destination, drop the tblImport.

I should also add, that you cannot drop the FK, import, then re-add the FK. The operation to re-add the FK will check the constraint, and fail.

You need to either 1) add a relationship entry that allows the import as I suggested above or 2) write an import script that satisfies the constraints of the FK, and only imports records where a relationship exists (ie. it would only import New York and Los Angeles).

Upvotes: 1

Related Questions