CODEWITHSUNDEEP
oracle-databaseplsql
Lolly
Lolly

Reputation: 36372

Insert into select query with cursor value

I want to write a SQK script with insert query in Oracle where one of the value will be fetched from cursor and rest all will be retrieved from table.

For example, consider Employee table:

Emp_No | Emp_Name

  1    |  AAA
  ...........

I am reading the table into a cursor.

Cursor c1 is select emp_no, emp_name from employee;

I am iterating the cursor and adding to a table along with information from another table.

for empCur in c1
loop

insert into employee_info(emp_no, emp_name, address, age, ... ) values (empCur.emp_no, empCur.emp_name, select t.address, t.age, ... from employee_temp_table t where t.emp_no=empCur.emp_no)

end loop;

Is my script valid? If not is there any other way to achieve it? Since few values are in cursor and few are in another table I am not sure how to handle this.

Upvotes: 0

Views: 14839

Answers (2)

JohnFL
JohnFL

Reputation: 52

If you want to use the cursor, why not just join the tables inside the cursor?

for empCur in ( select e.emp_no, e.emp_name, t.address, t.age ...
from employee e join employee_temp_table t on ( t.emp_no = e.emp_no )
) loop
insert into employee_info(...) values ( empCur.emp_no, ...);
end loop;

Or with a sql insert: (if you can choose sql over pl/sql - T Kyte says do it)

insert into employee_info
select e.emp_no, e.emp_name, t.address, t.age ...
from employee e join employee_temp_table t on ( t.emp_no = e.emp_no );

Upvotes: 1

Multisync
Multisync

Reputation: 8787

Your script isn't correct because this

select t.address, t.age, ... from employee_temp_table t where t.emp_no=empCur.emp_no

is not a valid expression. You may use a scalar subquery (one-row, one-column subquery) only, like this:

insert into t1(col1, col2, col3) values (1, (select col1 from t2), (select col2 from t2));

Or you may try insert from select:

for empCur in c1 loop
  insert into employee_info(emp_no, emp_name, address, age, ... ) 
  select empCur.emp_no, empCur.emp_name, t.address, t.age, ... from employee_temp_table t where t.emp_no=empCur.emp_no;
end loop;

Upvotes: 1

Related Questions