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.

CodePudding user response：

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

CodePudding user response：

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;