Insert mutliple records from a single record in the table using for loop in oracle

user_3516718 0
Viewed 31

In a table, I have a data which I mentioned below.

Driver_Name_1 Age   Driver_Name_2 Age   Driver_Name_3 Age
Tony          35    Mark          32

From the above one record, I have to insert into the another table with separate row for each driver using for loop. I have to check the first if Driver Name 1 is not null and then I have to insert the record with Driver Name 1 and age. And If Driver Name 2 is not null then I have to insert another record and go on.

plsql package oracle
2 Answers

You may use unpivot clause with exclude nulls (which is the default) to perform an insert with plain SQL.

insert into t
select
  name, age
from s
unpivot exclude nulls (
  (name, age) for grp in (
    (driver_name_1, age1) as 1,
    (driver_name_2, age2) as 2,
    (driver_name_3, age3) as 3
  )
)

select *
from t

DRIVER_NAME | AGE
:---------- | --:
Tony        |  35
Mark        |  32

db<>fiddle here

user_27787100

Straightforward & simple, as you described:

begin
  for cur_r in (select * from your_table) loop
    if cur_r.driver_name_1 is not null then
       insert into another_table (name, age) values (cur_r.driver_name_1, driver_age_1);
    end if;

    if cur_r.driver_name_2 is not null then
       insert into another_table (name, age) values (cur_r.driver_name_2, driver_age_2);
    end if;

    if cur_r.driver_name_3 is not null then
       insert into another_table (name, age) values (cur_r.driver_name_3, driver_age_3);
    end if;
  end loop;
end;
user_90979060
Related