select * from V$VERSION;

alter session set nls_date_format='yyyy-mm-dd'

create table data (id, company_name, vat_id, url, created) as select 1, 'aaa', '123', 'aaa.com', date'2021-05-01' from dual union all select 2, 'bbb', '111', 'bbb.de' , date'2021-05-01' from dual union all select 3, 'ccc', '345', 'ccc.de ', date'2021-05-01' from dual union all select 5, 'ddd', '098', 'ddd.de' , date'2021-05-21' from dual

create table data_hist (tag, id, company_name, vat_id, url, delete_flag) as select date'2021-05-25', 1, 'aaa', '123', 'aaa.com', null from dual union all select date'2021-05-25', 3, 'ccc', '345', 'ccc.de ', null from dual union all select date'2021-05-25', 4, 'xxx', '789', 'xxx.com', 1 from dual union all select date'2021-05-21', 1, 'aaa-2', '123', 'aaa.com', null from dual union all select date'2021-05-21', 3, 'ccc', '345-2', 'ccc.de ', null from dual union all select date'2021-05-20', 1, 'aaa-3', '123-3', 'aaa.com', null from dual union all select date'2021-05-20', 2, 'bbb', '111', 'bbb.de ', null from dual

select * from data;

select * from data_hist;

with param (asofday) as ( select date'2021-05-20' from dual ), t (tag, id, company_name, vat_id, url, delete_flag) as ( select date'9999-12-31', id, company_name, vat_id, url, null from data, param where created <= param.asofday union all select tag, id, company_name, vat_id, url, delete_flag from data_hist) select lastchanged, id, company_name, vat_id, url, delete_flag from t t1 cross apply ( select max (t2.tag) keep (dense_rank last order by tag desc) lastchanged from t t2, param where t2.id = t1.id and t2.tag >= param.asofday group by t2.id ) where tag = lastchanged order by id

create global temporary table stage (id, company_name, vat_id, url, created) as select * from data where 1=0

create or replace type stagerow is object ( id int, company_name varchar(96), vat_id varchar(96), url varchar(96), created date, flagexists int) /

create or replace type stagetab is table of stagerow /

insert into stage select 1, 'aaa', '123', 'aaa.com', date'2021-05-01' from dual union all select 2, 'bbb', '111', 'bbb.de' , date'2021-05-01' from dual union all select 3, 'ccc', '345', 'ccc.de ', date'2021-05-01' from dual union all select 4, 'xxx', '789', 'xxx.com', date'2021-05-01' from dual union all select 5, 'ddd', '098', 'ddd.de' , date'2021-05-21' from dual

select * from stage

declare st stagetab; begin select stagerow ( s.id, s.company_name, s.vat_id, s.url, s.created, case when d.id is null then 0 else 1 end) bulk collect into st from stage s left join data d on d.id = s.id; dbms_output.put_line ('staged '||sql%rowcount); insert into data_hist select trunc (sysdate), id, company_name, vat_id, url, null from data d where exists ( select 1 from table (st) s where s.id = d.id and s.flagexists = 1); dbms_output.put_line ('copied '||sql%rowcount); delete from data d where exists ( select 1 from table (st) s where s.id = d.id and s.flagexists = 1); dbms_output.put_line ('deleted '||sql%rowcount); insert into data select id, company_name, vat_id, url, created from table (st); dbms_output.put_line ('inserted '||sql%rowcount); end; /