2

I want to pull Few tables (33 Tables) from one DB server A to another DB server B. I have Schema ready with few datas in DB Server B but when I try to pull data from DB Server A to DB Server B,
I am getting following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "Table 1".

The conflict occurred in database"DB Server A", table "dbo.Parent Table", column 'xyz'.

The code which I used for this Operation:

INSERT INTO server1table
(X,Y,Z)
from server2.database.dbo.server2table

So what are the possible ways to do this operation taking care of key constraints?

  • 1
    Insert from the foreign table first. Make sure you preserve referential integrity, by ordering the inserts accordingly, in order of reference. – Remus Rusanu May 2 '17 at 6:47
  • @RemusRusanu thank you for the response, Is there any easy way to find out order of reference?Since there are more tables and are interrelated to one or more tables. – l.lijith May 2 '17 at 6:56
  • You should know the database schema if you work with it. Query sys.foreign_keys – Remus Rusanu May 2 '17 at 7:02
0

You have a few options.

  1. Copy data in the correct order. Find the parent child relationships and insert data to the parent tables first.
  2. Drop or disable the constraints, insert the data, then recreate or re-enable the constraints.
  3. Use a tool like Redgate SQL Data Compare, which will identify the heirachy and copy data in the correct order for you.

If you want to know more about how to find foreign key constraints there is a great post here

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.