LOOP through and select from multiple tables in SQL developer

Question

I am trying to loop through 5 tables all with a similar naming format. I am able to return the tables names by using the query

select table_name from all tables
where table_name like '
order by table_name

My intention is to insert the data from all the 5 tables into one table where a particular date condition is met. I know I can do this via union all but this makes my script very long as I have to paste the same script for 5 tables, I was wondering if there is a way to do without using union all but rather using dynamic sql? I am new to dynamic sql.

Answer 1

It might look like this:

begin
  for cur_r in (select table_name 
                from all_tables
                where table_name like 'ABC%'
               )
  loop
    execute immediate 
    'insert into target_table (id, name, address) ' ||
    'select id, name, address from ' || cur_r.table_name ||
    '  where date_column = ' || trunc(sysdate);
  end loop;
end;
/                 

• loop through all_tables, fetching only those you're interested in
• compose insert statement, presuming that all tables (from previous step) share the same set of common columns
• include where clause, if you want