Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are two schemas in Oracle11g. For example db1 and db2.

They have same structure of tables and different data to each other.

Now I want to merge data on db1 with data on db2 with no duplication.

But I can’t, duplication is happened.

I tried two different ways like bellow:

  1. Import from dumpFile.db1 into db1 then import from dumpFile.db2 into db1.

  2. After import data into both schemas and executo direct path insert.

    insert/*+ APPEND */ into db1.myTable (SELECT * FROM db2,myTable);.

Are there good way to merge without duplication?

share|improve this question
    
Doesn't the Oracle MERGE statement work for you? –  Mat Nov 29 '12 at 10:13
    
Actually I want merge any schema without ON condition or WHERE condition. I mean unless I know about table structure especially what primary key is, I want merge them if they have same structure. But As Leigh Riffel answered bellow ,I have to know about fields in each table. –  user1345414 Nov 30 '12 at 4:17

2 Answers 2

up vote 1 down vote accepted

I use impdp command with CONTENT=DATA_ONLY DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS . It seems no duplication.

share|improve this answer
1  
duplication is avoided by the primary/unique key contraints. so a record is a duplicate of another record of the same table exactly if they have the same value for the primary key or at least one unique key. that seems to be a reasonable definition if no surrogate keys are used. –  miracle173 Dec 3 '12 at 6:58

Just add a WHERE clause to your select that eliminates the duplicates:

INSERT /*+ APPEND */ INTO db1.myTable (
   SELECT * FROM db2.myTable b WHERE NOT EXISTS 
      (SELECT 1 FROM db1.myTable a WHERE a.ID = b.ID)
   );

If you don't have a field like ID that is unique across the two tables, then you will need to compare more fields, perhaps all of them.

share|improve this answer
    
Sounds good.I'll try it. –  user1345414 Nov 30 '12 at 0:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.