Oracle MERGE
Data management activities involve insert, update and delete operations ( CRUD ). With the MERGE feature, data insert, update and delete operations can be done with a merge statement. The example shown here does an insert and an update. This feature is very commonly used in ETL processes and in data warehouse loads to achieve optimum load performance.

Note: FGAC not implemented during merge statement
   
Oracle MERGE
MERGE INTO mfg_schedule_detail m
 USING 
  (SELECT
     mfg_schedule_id, mfg_seq_id, mfg_completion_date,
     qty_issued, qty_completed, qty_scrapped,
     current_status,
     SYSDATE run_date
   FROM  mfg_schedule_stg
   WHERE created_date_id = TO_CHAR(sysdate-1,'yyyymmdd')
  ) s
 ON (m.mfg_scedule_id = s.mfg_scedule_id)
 WHEN MATCHED THEN  UPDATE SET
     m.mfg_completion_date = s.mfg_completion_date,
     m.qty_completed = s.qty_completed,
     m.qty_scrapped = s.qty_scrapped,
     m.current_status = s.current_status
     m.updated_date = s.run_date
 WHEN NOT MATCHED THEN  INSERT
    (mfg_schedule_id,
     mfg_seq_id,  mfg_completion_date,
     qty_issued,  qty_completed,  qty_scrapped,
     current_status,
     created_date)
   VALUES
    (s.mfg_schedule_id, s.mfg_seq_id, s.mfg_completion_date,
     s.qty_issued, s.qty_completed, s.qty_scrapped,
     s.current_status,
     s.run_date);

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 05, 2012

  23702