How to fetch merge data in Oracle

Question

I have a table SALARY_MASTER which contains empid and salary. Below is the table structure.

EMPID   SALARY
------- ------
10001   12000
10002   60000
10003   25000
10004   35000
10005   15000
10006   24000
10007   85000
10008   75000
10009   65000
10010   67000
10011   95000
10012   23000
10013   48000
10014   25000
10015   35000

Another table named SALARY_CURRENT with same columns. Below is the sample data.

EMPID   SALARY
------- ------
10001   24000
10003   36000
10005   23000
10007   99000
10009   79000
10016   52000
10017   98000
10018   63000
10019   77000
10020   47000
10021   35000

This table contains updated salary for employees. In every quarter I get this table from higher authority and I need to update the SALARY_MASTER table based on the SALARY_CURRENT table. So I used merge command in this case. Below is my merge statement:

MERGE into SALARY_MASTER SM
USING
    SALARY_CURRENT SC
ON
    (SM.EMPID = SC.EMPID)
WHEN MATCHED THEN
    UPDATE SET SM.SALARY = SC.SALARY
WHEN NOT MATCHED THEN
    INSERT (SM.EMPID,SM.SALARY) VALUES(SC.EMPID,SC.SALARY);

After merging my SALARY_MASTER, the table is like below:

EMPID   SALARY
------- ------
10001   24000
10002   60000
10003   36000
10004   35000
10005   23000
10006   24000
10007   99000
10008   75000
10009   79000
10010   67000
10011   95000
10012   23000
10013   48000
10014   25000
10015   35000
10016   52000
10017   98000
10018   63000
10019   77000
10020   47000
10021   35000

I want only affected rows. My output table will be like below:

EMPID   SALARY  STATUS
------- ------- ------
10001   24000   UPDATE
10002   60000   NONE
10003   36000   UPDATE
10004   35000   NONE
10005   23000   UPDATE
10006   24000   NONE
10007   99000   UPDATE
10008   75000   NONE
10009   79000   UPDATE
10010   67000   NONE
10011   95000   NONE
10012   23000   NONE
10013   48000   NONE
10014   25000   NONE
10015   35000   NONE
10016   52000   INSERT
10017   98000   INSERT
10018   63000   INSERT
10019   77000   INSERT
10020   47000   INSERT
10021   35000   INSERT

Or:

EMPID   SALARY  STATUS
------- ------- ------
10001   24000   UPDATE
10003   36000   UPDATE
10005   23000   UPDATE
10007   99000   UPDATE
10009   79000   UPDATE
10016   52000   INSERT
10017   98000   INSERT
10018   63000   INSERT
10019   77000   INSERT
10020   47000   INSERT
10021   35000   INSERT

I am using Oracle 11g. Actual table contains more than 300k values.

Answer 1

Use the EXIST/NOT EXISTS with UNION will solve your problem, check out the below code:

select sm.empid,sm.salary,'UPDATE' status
from salary_master sm
where exists (select 1 from salary_current sc where sc.empid = sm.empid)
union
select sc.empid,sc.salary,'INSERT' status
from salary_current sc
where not exists (select 1 from salary_master sm where sm.empid = sc.empid);

Answer 2

Maybe you want to have a log table for the MERGEs. Using your test data (table names changed...)

create table mergelog
as
select empid, salary, 'unchanged' as status
from salarymaster ;

Then, run a MERGE, very similar to your original, which modifies the data in the log table (your original tables remain untouched).

merge into mergelog M
using
  currentsalaries C
  on ( M.empid = C.empid )
when matched then
  update set 
    M.salary = C.salary
  , M.status = 'UPDATED'
  where M.salary <> C.salary
when not matched then
  insert ( M.empid, M.salary, M.status )
    values ( C.empid, C.salary, 'INSERTED' )
;

The log table now contains ...

select * from mergelog order by empid ;

EMPID  SALARY  STATUS     
10001  24000   UPDATED    
10002  60000   unchanged  
10003  36000   UPDATED    
10004  35000   unchanged  
10005  23000   UPDATED    
10006  24000   unchanged  
10007  99000   UPDATED    
10008  75000   unchanged  
10009  79000   UPDATED    
10010  67000   unchanged  
10011  95000   unchanged  
10012  23000   unchanged  
10013  48000   unchanged  
10014  25000   unchanged  
10015  35000   unchanged  
10016  52000   INSERTED   
10017  98000   INSERTED   
10018  63000   INSERTED   
10019  77000   INSERTED   
10020  47000   INSERTED   
10021  35000   INSERTED   

21 rows selected.

Tested w/ Oracle 12c and 11g (see dbfiddle.uk).