Merge Data From Many Table In Oracle

Merge Data From Many Table In Oracle: "

If we have many data in database, and then we want to merge data from other table. Usually this concept for transformation data in data warehouse process. In processing data warehouse we know for ETL (Extraction, Transformation, Loading) concept, in transformation step is we must merge data from many table schema.

Now, in this tutorial, I want to share how to merge data from manya table in Oracle. Read step by step below:

1. In this case, we have data in table dvdlama in user schema legacy. So we want to merge that data from dvdlama to dvd. We merge data in transformasi schema.


2. We have table with structure table of dvd like that in transformasi schema
   

   create table dvd(
   id number,
   title varchar2(500),
   media varchar2(20),
   category varchar2(100)
   );

   
   

   And column id is auto increment set. (read how to create auto increment column)



3. Check “select” access from transformasi schema to legacy schema.
   

   select * from legacy.dvdlama

   
   

   If you have no access in legacy schema, you must do grant for access legacy from transformation schema. And that providing access is the system schema, so you run this query in system schema:

   
   

   grant select on legacy.dvdlama to transformasi

   
   


4. If you’ve could to access legacy from transformasi, now we can to merge data from legacy.dvdlama to dvd in transformasi schema. Follow this query:
   

   merge into DVD d using legacy.DVDLAMA dl
   on (d.title=dl.title)
   when matched then update set
   d.category=dl.genre, d.media=dl.media
   when not matched then insert (id, title, media, category)
   values (dvd_id_seq.nextval, dl.title, dl.media, dl.genre);

   
   

   Explanation is:
   
   Merge data from DVDLAMA in legacy schema to DVD with condition is title column from DVD is equals with title column in DVDLAMA. If title in DVD is equals with title in DVDLAMA, so data will be update. And if not equals, data will be insert new record in id, title, media and category in DVD with ID is auto-increment (dvd_id_seq.nextval).
   
   So data will merge to DVD table in transformasi schema.

"