Efficiently modifying dataset from stage - proof of concept in SQLite

output.txt

SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
sqlite> -- SQLite
sqlite> -- Test: sqlite :memory: < %
sqlite> 
sqlite> create table t1 (
   ...>   bk int primary key
   ...>   , a1 varchar(20)
   ...> );
sqlite> 
sqlite> insert into t1 (bk, a1) values
   ...>     (1, 'A')
   ...>   , (2, 'B')
   ...>   , (3, 'C')
   ...>   , (4, 'D')
   ...> ;
sqlite> 
sqlite> select * from t1;
bk          a1        
----------  ----------
1           A         
2           B         
3           C         
4           D         
sqlite> 
sqlite> create table stage (
   ...>   bk int primary key
   ...>   , a1 varchar(20)
   ...> );
sqlite> 
sqlite> -- Remove 3
sqlite> -- New 5
sqlite> -- Change 2
sqlite> insert into stage (bk, a1) values
   ...>     (1, 'A')
   ...>   , (2, 'Z') -- Changed to Z
   ...>   -- , (3, 'C') -- Removed
   ...>   , (4, 'D')
   ...>   , (5, 'E') -- New
   ...> ;
sqlite> 
sqlite> -- select * from stage;
sqlite> 
sqlite> -- Delete rows
sqlite> delete from t1
   ...> where bk in (
   ...>     select bk
   ...>     from t1
   ...>     where not exists (
   ...>         select bk
   ...>         from stage
   ...>         where stage.bk = t1.bk
   ...>       )
   ...>   )
   ...> ;
sqlite> 
sqlite> -- Upsert the rest from stage
sqlite> insert into t1 (bk, a1)
   ...> select
   ...>   bk
   ...>   , a1
   ...> from
   ...>   stage
   ...> where true
   ...> on conflict(bk) do update set a1 = excluded.a1 where a1 <> excluded.a1
   ...> ;
sqlite> 
sqlite> select * from t1;
bk          a1        
----------  ----------
1           A         
2           Z         
4           D         
5           E         
sqlite> 
sqlite> 

sqlite_test_script_update_stage_changes_to_dataset.sql

-- SQLite
-- Test: !sqlite3 -interactive -header :memory: < %

create table t1 (
  bk int primary key
  , a1 varchar(20)
);

insert into t1 (bk, a1) values
    (1, 'A')
  , (2, 'B')
  , (3, 'C')
  , (4, 'D')
;

select * from t1;

create table stage (
  bk int primary key
  , a1 varchar(20)
);

-- Remove 3
-- New 5
-- Change 2
insert into stage (bk, a1) values
    (1, 'A')
  , (2, 'Z') -- Changed to Z
  -- , (3, 'C') -- Removed
  , (4, 'D')
  , (5, 'E') -- New
;

-- select * from stage;

-- Delete rows
delete from t1
where bk in (
    select bk
    from t1
    where not exists (
        select bk
        from stage
        where stage.bk = t1.bk
      )
  )
;

-- Upsert the rest from stage
insert into t1 (bk, a1)
select
  bk
  , a1
from
  stage
where true
on conflict(bk) do update set a1 = excluded.a1 where a1 <> excluded.a1
;

select * from t1;