transfer data from one database to another regarding keys

Question

How can i transfer rows from two tables (Patient and ContactDetails) from DB1 to DB2? Both DBs, have already these 2 tables with data. i just want to add data from these two tables from db1 to db2.

i tried following that

but it didnt work, because there are some rows with the same keys and overwrite is forbidden.

is there an other way to do it? or am i missing something?

patient and contactdetails relationship is

patient inner join contactdetails
(foreign_key)patient.contactdetailsid = (primary_key)contactdetails.id

Do you just want to append the data and keep the existing data, or are you trying to replace the whole table? — Johnny Bones, Sep 10 '14 at 14:03
What's the purpose of this copy? Is it to keep the 2 databases in sync? Or DB1 is an entry system and DB2 is the mothership? — Code Different, Sep 10 '14 at 14:25

score 0 · Answer 1 · edited May 23 '17 at 12:10

loop on the source contactdetails table, insert each row one a time saving in a temp table the old contactdetail id and the matching new contactdetail id (here is an example of sql loop).
the temp table should be something like:

create @temptableforcopy table (
 oldcontactdetailsid [insertheretherightdatatype],
 newcontactdetailsid [insertheretherightdatatype]
)

copy the data from the patient table joined to the temp table used for the previous step like this:

insert into newdb.newschema.patient (contactdetailsid, field1, field2, ...)
select TT.newcontactdetailsid,
       old.field1,
       old.field2,
       ...
from   olddb.oldschema.patient old
       join @temptableforcopy TT on TT.oldcontactdetailsid = old.contactdetailsid

please note that my proposal is just a wild guess: you gave no information about structure, keys, constraints, no detail about which key is preventing the copy with which error message, the solution you already discarded, the amount of data you have to deal with...

transfer data from one database to another regarding keys

1 Answers1