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


there 2 databases db_main , db_backup in sql server 2008.

i want copy data of table1 db_backup table1 of db_main.

the structure of tables in both database same. both tables in both database have foreign key, primary key constraint.

when try copy data of table1 db_backup table1 of db_main query:

insert [db_main].[table1]      select *      [db_backup].[table1];  

i foreign key error.

the insert statement conflicted foreign key constraint "fk_table1_table3". conflict occurred in database "db_main", table "table3", column 'requestid'.

please let me know simple way copy records of table1 db_backup table1 of db_main, without violating constraint?

please reply

what means example trying insert record table1 has example requestid = 75. foreign key constraint means there must record requestid = 75 in table3.... , there isn't.

so means need load data table3

to find records causing issue run

select distinct requestid [db_backup].[table1] 

some of these request id's need 'parent' record in table3

to find specific ones run

select requestid [db_main].[table3]  request_id not in (     select distinct requestid [db_backup].[table1]     ) 

you need insert these table3:

insert table3(request_id, othercolumn)  select requestid, othercolumn [db_backup].[table3]  request_id not in (     select distinct requestid [db_main].[table3]     ) 

then can load other records.


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -