CODEWITHSUNDEEP
sqloracle-databaseplsql
mrm95
mrm95

Reputation: 43

How to insert data from one table to another in oracle from a stored procedure?

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

Answers (1)

Michal J Figurski
Michal J Figurski

Reputation: 1361

The combination of LIMIT and FETCH may be helpful. More here: https://www.oracletutorial.com/oracle-basics/oracle-fetch/

Upvotes: 1

Related Questions