Reputation: 43
I have a daily table and a history table in my Oracle database. At the end of each day, I am looking to insert data from the daily table to the history table, then truncate the daily table. The catch is I am must insert the data in chunks of 5000 entries, and we are working with about 5 million entries total. I'm very new to PL/SQL and could use some help writing the stored procedure that would work for this scenario.
The first procedure using an all-at-once approach looks like this:
create or replace procedure test
is ts timestamp := current_timestamp;
begin
insert into t_history
select t.*, substr(USER, 1, 15), ts
FROM t;
end test;
My question is, how can I refactor the above approach to load the data in chunks of 5000 entries and commit after each insert, rather than all at once?
Any help is greatly appreciated.
Upvotes: 0
Views: 867
Reputation: 1361
The combination of LIMIT
and FETCH
may be helpful. More here: https://www.oracletutorial.com/oracle-basics/oracle-fetch/
Upvotes: 1