Is there a way to transactionally move rows from one columnstore table (call it the
source) to another table (call it the
target) while the source table is being written?
For example, I’d ideally like to do something like this:
start transaction; insert into facts_app_metrics select * from facts_app_metrics_staging where epoch = ...; delete from facts_app_metrics_staging where epoch = ...; commit;
However, if the source table is being written to during this, then it seems that an
insert into the source table (from a separate thread) between the above
insert into and
delete from statements can result in the row not being moved but also being deleted.
I can change our implementation to have some sort of incrementing identifier so that we can add another condition onto the
insert into /
delete from statements. However, I was hoping to avoid that if possible. Any thoughts?