4

I am preparing a stored procedure in oracle. In this procedure I have a select statement which will result in multiple rows. Now I want to insert these rows to another table.

Please someone tell me it is really urgent.

UPDATE:

One thing that I forgot to mention is that I also have to add another column in my result of select statement. This column will contain value of a parameter in this stored procedure.

Below is the sample stored procedure for this

Create or Replace PROCEDURE "My Procedure" 
  (
    my_id in number,
    nric in VARCHAR2
  )
BEGIN
insert into new_table(my_new_id,field1, field2)
select my_id,table1.field1, table2.field2 
from table1, table2 
where table1.age=table2.age AND table1.my_nric=nric;
END

In above sample I have my_id in procedure parameter and want to insert that in new_table for each result entry from table1 and table2.

Nikhil Gaur
  • 1,280
  • 3
  • 19
  • 40
  • possible duplicate of [INSERT SELECT statement in Oracle 11G](http://stackoverflow.com/questions/7323407/insert-select-statement-in-oracle-11g) – Ollie Jul 24 '12 at 13:41
  • 3
    If it is "really urgent" perhaps you should read the similar answers on this site or the documentation about the insert statement online for yourself: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm – Ollie Jul 24 '12 at 13:43
  • What is your question? What is the error/problem? –  Oct 02 '13 at 06:11

1 Answers1

11

You can try this

INSERT INTO myTable(field1, field2, field3)
   SELECT field1, field2, field3
     FROM anotherTable
       WHERE thisCondition = 'mycondition';
codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • your code works with only one table while I need two. Anyways my problem hi solved. Thanks for you help. – Nikhil Gaur May 03 '13 at 12:22
  • 1
    No problem. You could always join to as many tables you want. It's just a select statement, no limit to number of tables you need. Cool! – codingbiz May 03 '13 at 13:59