Forum Stats

  • 3,871,979 Users
  • 2,266,360 Discussions


Delta load logic using flag status or latest insert/update date

user12251389 Member Posts: 335 Blue Ribbon
edited Jan 28, 2021 11:40AM in SQL & PL/SQL

I have below merge query which is working fine and perform delta logic but not completely.

In this Merge query i am trying to perform Delta import logic using Insert/Update/Delete. For new data with combination of Unique column(ID_LL_ ID_UU, TKR) i want to insert data into TEST_RUA_MER table otherwise update data. Delete will delete the data from test_rua_mer table which does not exist in test_rua table.

But now i want change the logic in this merge query. I dont want to delete the data. The logic for Update and Insert will be same. I want to create for example new field say "Delta_Date" of Date datatype in TEST_RUA_MER table where based on the comparision of table TEST_RUA , i want to update this field with the current date for all the rows for which the data has been updated or inserted in table TEST_RUA_MER. So that whenever i query on this table i can simple use this "Delta_Date" with the current date to recognize which and how many data has been Inserted or Updated.

For the matching row between table TEST_RUA_MER and TEST_RUA we will not do anything.

As we are using Union All in Merge query, if required we can also create dummy field same like "Delta_Date" of Date datatype in TEST_RUA table.

This question is extending my previous question where i mentioned the datasets::

Below is the Merge query :

merge into test_rua_mer d
using (
 select a.* 
    , case
      when target = 'D' and cnt_id = cnt_vals then 'D'
      when target = 'S' and cnt_id = 1 and cnt_vals = 1 then 'I'
      when target = 'S' and cnt_id = 2 and cnt_vals = 1 then 'U'
      else 'X' -- ignore
     end action
 from (
    count(*) over (partition by id_ll, id_uu, tkr) cnt_id
  , count(*) over (partition by id_ll, id_uu, tkr, class, name) cnt_vals
  , max(rwid) over (partition by id_ll, id_uu, tkr) rwid 
  , class, id_ll, id_uu, tkr, name, target 
  from (
   select class, id_ll, id_uu, tkr, name, 'D' target, rowid rwid
   from test_rua_mer
   union all
   select class, id_ll, id_uu, tkr, name, 'S' , null
   from test_rua
  ) a
 ) a
) s
on (d.rowid = s.rwid)
when not matched then
 insert (d.class, d.id_ll, d.id_uu, d.tkr,
 values (s.class, s.id_ll, s.id_uu, s.tkr,
 where action = 'I'
when matched then
 update set 
  d.class = s.class,  =
 where action in ('D', 'U')  
 delete where action = 'D'; 

Below is the data set in fiddle :

Best Answer