• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • Last Modified:

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

0
biglarrrr
Asked:
biglarrrr
  • 4
1 Solution
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now