Need some suggestions how to achieve pl/sql insert looping over two tables-CodePudding

I have three tables employee, department, hrrecords

employee table:

emp id	empname
101	aaa
102	bbb
103	ccc

Department Table:

dep id	dep name
1	X
2	Y
3	Z

I need to insert data from the above two tables into table 'hrrecords' like this

emp id	dep id
101	1
101	2
101	3
102	1
102	2
102	3
103	1
103	2
103	3

I wanted to do this using loops, any suggestions please ?

CodePudding user response：

Its not the best solution, but it should work in your case:

insert into hrrecords
select emp_id, dep_id
from employee, department

CodePudding user response：

I wanted to do this using loops, any suggestions please ?

You can create a procedure like this:

CREATE OR REPLACE PROCEDURE loop_insert IS
  cursor c1 
  is
  select emp_id 
  from employee;
  
  cursor c2
  is
  select dep_id 
  from Department;
  
BEGIN
    for v1 in c1 loop
        for v2 in c2 loop
            insert into mytable values(v1.emp_id, v2.dep_id);
        end loop;
    end loop;
END loop_insert;
/

and then you can execute it like this:

begin
    loop_insert;
end;
/

DEMO