CODEWITHSUNDEEP
sql-serverdatabasedata-transfer
Daggy
Daggy

Reputation: 3

transfer data from one database to another regarding keys

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

Upvotes: 0

Views: 147

Answers (1)

Paolo
Paolo

Reputation: 2254

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...

Upvotes: 0

Related Questions