Copy data between tables in different databases without PK's ( like synchronizing )

Question

I have a table ( A ) in a database that doesn't have PK's it has about 300 k records.

I have a subset copy ( B ) of that table in other database, this has only 50k and contains a backup for a given time range ( july data ).

I want to copy from the table B the missing records into table A without duplicating existing records of course. ( I can create a database link to make things easier )

What strategy can I follow to succesfully insert into A the missing rows from B.

These are the table columns:

IDLETIME    NUMBER 
ACTIVITY    NUMBER  
ROLE    NUMBER   
DURATION    NUMBER  
FINISHDATE  DATE 
USERID  NUMBER
.. 40 extra varchar columns here ...

My biggest concern is the lack of PK. Can I create something like a hash or a PK using all the columns?

What could be a possible way to proceed in this case?

I'm using Oracle 9i in table A and Oracle XE ( 10 ) in B

The approximate number of elements to copy is 20,000

Thanks in advance.

Answer 1

As long as no duplicate rows should exist in the table, you could apply a Unique or Primary key to all columns. If the overhead of a key/index would be to much to maintain, you could also query the database in your application to see whether it exists, and only perform the insert if it is absent

Answer 2

If the data volumes are small enough, I'd go with the following

CREATE DATABASE LINK A CONNECT TO ... IDENTIFIED BY ... USING ....;
INSERT INTO COPY
SELECT * FROM table@A
MINUS
SELECT * FROM COPY;

You say there are about 20,000 to copy, but not how many in the entire dataset. The other option is to delete the current contents of the copy and insert the entire contents of the original table.

If the full datasets are large, you could go with a hash, but I suspect that it would still try to drag the entire dataset across the DB link to apply the hash in the local database.