Insert records from one table to another without violating any constraint in SQL Server database

Question

There are 2 databases DB_Main and DB_Backup in SQL Server 2008.

I want to copy the data of Table1 from DB_Backup into Table1 of DB_Main.

The structure of all the tables in both the database is same. Both the tables in both the database have foreign key, primary key constraint.

When I try to copy data of Table1 from DB_Backup into Table1 of DB_Main with this query:

Insert into [DB_Main].[Table1] 
    Select * 
    from [DB_Backup].[Table1];

I get this foreign key error.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Table1_Table3". The conflict occurred in database "DB_Main", table "Table3", column 'RequestID'.

Please let me know any simple way to copy all the records of Table1 from DB_Backup into Table1 of DB_Main, without violating any constraint?

Please reply

Please tag the database type (SQL Server?) This error means that you also need to load data into `Table3`. There is a piece of data in a column in `Table1` that needs to first exist in `Table3`. Take a look at the foreign key and work out what column needs the data. — Nick.Mc, Aug 25 '15 at 03:35
@Nick.McDermaid: Thank You for the reply. `Table3` already have data in it. Its just due to some reason `Table1` all the data got deleted from DB_Main database so I am insert the backup data from DB_Backup database. Please let me know what shall I change in my query ? — timz_123, Aug 25 '15 at 03:45
Did you check the constraint? Did you do any reading on foreign key constraints? I suggest you don't undertake a data patching exercise without knowing about foreign key constraints. Anyway I can't tell you what to change as you have not posted the constraint definition. We need to know what column in `Table1` the constraint is attached to. — Nick.Mc, Aug 25 '15 at 04:13
@Nick.McDermaid: I request you to please review the error again in my posted question. I had mentioned the column name `RequestID` this column is Foreign Key in `Table1` and Primary Key of `Table3`. Please note that I am new in Database. Please let me know what other info you need related to the error, I will let you know about it. Please reply — timz_123, Aug 25 '15 at 04:24
What this means is that for example you are trying to insert a record into `Table1` which has `RequestID` = 75. The foreign key constraint means that there _must_ be a record with `RequestID` = 75 in `Table3`.... and there currently isn't. So this means you also need to load data into `Table3`. Does that make sense? — Nick.Mc, Aug 25 '15 at 04:30
In `Table3' I have record with `RequestID=75` (primaryKey) , but `Table1` is empty , in which RequestID is foreign Key. — timz_123, Aug 25 '15 at 04:50
75 was an example. I'm just guessing. You are inserting data into `Table1`. FInd all the keys with `Select DISTINCT RequestID from [DB_Backup].[Table1]`. Now find the ones that are missing from the foreign table with this: `Select DISTINCT RequestID from [DB_Main].[Table3] WHERE Request_ID NOT IN (Select DISTINCT RequestID from [DB_Backup].[Table1])`. Do you get any? These are the ones that need to first be inserted into `Table3` — Nick.Mc, Aug 25 '15 at 04:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/87853/discussion-between-user3196511-and-nick-mcdermaid). — timz_123, Aug 25 '15 at 05:56

score 0 · Accepted Answer · answered Aug 25 '15 at 06:41

What this means is that for example you are trying to insert a record into Table1 which has for example RequestID = 75. The foreign key constraint means that there must be a record with RequestID = 75 in Table3.... and there currently isn't.

So this means you also need to load data into Table3

To find the records actually causing the issue run

Select DISTINCT RequestID from [DB_Backup].[Table1]

Some of these request ID's need a 'parent' record in Table3

To find the specific ones run

Select RequestID from [DB_Main].[Table3] 
WHERE Request_ID NOT IN (
    Select DISTINCT RequestID from [DB_Backup].[Table1]
    )

You need to insert these into Table3:

insert into Table3(Request_ID, OtherColumn)

Select RequestID, OtherColumn from [DB_Backup].[Table3] 
WHERE Request_ID NOT IN (
    Select DISTINCT RequestID from [DB_Main].[Table3]
    )

Then you can load your other records.

score -2 · Answer 2 · edited May 23 '17 at 11:58

-2

Drop or Disable constraint FK_Table1_Table3 before inserting and enable after it.

To Disable:

ALTER TABLE Table1 NOCHECK CONSTRAINT ALL

or

ALTER TABLE Table1 NOCHECK CONSTRAINT FK_Table1_Table3

Source

to Enable

ALTER TABLE Table1 CHECK CONSTRAINT ALL

or

ALTER TABLE Table1 CHECK CONSTRAINT FK_Table1_Table3

edited May 23 '17 at 11:58

Community

1
1

answered Aug 25 '15 at 03:34

Viktor Bardakov

866
6
16

and violate database integrity? – Nick.Mc Aug 25 '15 at 03:34
Thank you for the reply. Please let me know how to do that ? – timz_123 Aug 25 '15 at 03:45
@Nick.McDermaid Yep. This way is use to load big amount of data in short time. Of course you should check itegrity by your own before load of solve consistency problems after it. – Viktor Bardakov Aug 25 '15 at 04:02
@ViktorBardakov: Thank you for the reply. I just want to confirm that doing this will not harm any data or Table structure in Database ?? as **DB_Main** is the Live database I am using for my application. Please reply – timz_123 Aug 25 '15 at 04:12
1

Yes it will harm data. You have a foreign key issue and you don't fix that by turning off the foreign key. Its like if you see a burglar you just turn off the burglar alarm. You fix it by fixing the data. – Nick.Mc Aug 25 '15 at 04:15
@Nick.McDermaid: Thank you for the reply. Noted. – timz_123 Aug 25 '15 at 04:20

Insert records from one table to another without violating any constraint in SQL Server database

2 Answers2