PL/SQL insert using hard-coded values and a select

Hi, I know this is probably going to be an easy one for most of you but I just can't figure it out. I'm not exactly a PL/SQL expert and I hope someone can help me out. I'm working on an Oracle 10g db and I need to insert data into a table. Simple enough... The table has 3 columns - colA will always have a value of 1, colB will be gotten from a different table using a pretty simple select and colC will always be NULL.

I've tried my hand at using PL/SQL :) but of course it didn't work. I figured I'd need to use a cursor since I'm going to be getting back more that 1 result on my select. Am I even a little close?

Thanks,
biglarrrr
DECLARE
CURSOR curs IS
select a.colB
from table2
where blahblahblah;
 
curs_rec curs%rowtype;
Begin
FOR curs_rec IN curs LOOP
insert into table1(colA, colB, colC) values (1, curs_rec, null);
 
end loop;
 
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('No more Data to be processed');
 
end;

Open in new window

biglarrrrAsked:
Who is Participating?
 
sdstuberCommented:
instead of iterating row-by-row (also known as slow-by-slow)

just insert all of the data at once...


BEGIN
    INSERT INTO table1(cola, colb, colc)
        (SELECT 1, colb, NULL
           FROM table2
          WHERE blahblahblah);
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        DBMS_OUTPUT.put_line('No more Data to be processed');
END;

Open in new window

0
 
sdstuberCommented:
if you want to see the iterative way, you almost had it, the only thing you were missing was the column name
DECLARE
CURSOR curs IS
select a.colB
from table2
where blahblahblah;
 
curs_rec curs%rowtype;
Begin
FOR curs_rec IN curs LOOP
insert into table1(colA, colB, colC) values (1, curs_rec.colb, null);
 
end loop;
 
EXCEPTION
WHEN NO_DATA_FOUND
THEN DBMS_OUTPUT.PUT_LINE('No more Data to be processed');
 
end;
 

Open in new window

0
 
sdstuberCommented:
and, you aren't going to use the explicitly declare curs_rec variable.  

The FOR LOOP creates its own variable.
So, the curs_rec you see in the FOR loop is NOT the same one you declared above.

but it's moot,  you should do the insert directly from the select anyway
0
 
biglarrrrAuthor Commented:
Hi sdstuber. Thanks so much for this. I really appreciate it and your feedback.

Thanks,
biglarrrr
0
 
sdstuberCommented:
glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.