fetch multiple rows, store in 1 variable and insert into table

Question

I have to fetch bulk record and insert into table using loop I have little confusion how to fetch and insert record using loop. Below I have shared what I have done so far .

declare
    stud_Id varchar;
begin            
    stud_Id := select student_id from student_backup where is_active_flg ='Y';

    for i in 1 ..stud_Id.count
    loop
        insert into users(student_id,password,status) values(stud_Id(i),'password','status')
        where not exists (select student_id from users where student_id=stud_Id(i))
    end loop;

    commit;
end;
/

should i have to use array variable or something else for student_id — AbdulRaheem
– AbdulRaheem, Commented Jun 1, 2019 at 7:58
try this link docs.oracle.com/cd/A97630_01/appdev.920/a96624/a_samps.htm — Satya
– Satya, Commented Jun 1, 2019 at 8:00

Barbaros Özhan · Accepted Answer · 2019-06-01 14:41:44Z

2

You can use the following :

declare
  stud_Id student_backup.student_id%type;
begin
     select nvl(max(student_id),0) into stud_Id 
       from student_backup 
      where is_active_flg ='Y';

   if stud_Id >0 then
    for i in 1 ..stud_Id
    loop
       insert into users(student_id,password,status) 
       select b.student_id,'password','status'
         from student_backup b 
         left join users u on b.student_id = u.student_id
        where is_active_flg ='Y'
          and b.student_id = i;
    end loop;
   end if; 
    commit;
end;
/

Demo

P.S. If I understood you want to perform, you don't need to use for loop(including if statement) and the select statement in the beginning, but directly apply the insert statement by removing the part and b.student_id = i. So, convert your block to the one as below :

declare
  stud_Id student_backup.student_id%type;
begin

       insert into users(student_id,password,status) 
       select b.student_id,'password','status'
         from student_backup b 
         left join users u on b.student_id = u.student_id
        where is_active_flg ='Y' ;
    commit;
end;
/

edited Jun 1, 2019 at 14:41

answered Jun 1, 2019 at 8:14

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

AbdulRaheem Over a year ago

dear can you please elaborate why you use max(student_id) instead of count(student_id).

Barbaros Özhan Over a year ago

hi @AbdulRaheem. I thought you need to scan all the possible values of student_id. If some not exists between 1 and max(student_id), then it's already skipped, and iterate to the max value.

Barbaros Özhan Over a year ago

@AbdulRaheem assume you have 50 students with ID through 1 to 100. Whenever 1..count(student_id) is used for the loop, the ID with 51 to 100 will not be considered.

William Robertson Over a year ago

@BarbarosÖzhan you won't hit a no_data_found exception with a max query.

Barbaros Özhan Over a year ago

Hi @AbdulRaheem. I don't use skype. My e-mail is [email protected]

|

Popeye · Accepted Answer · 2019-06-03 11:55:06Z

0

Abdul,

I think you are searching for the following:

BEGIN

INSERT INTO USERS 
SELECT STUDENT_ID, PASSWORD , STATUS
FROM student_backup
WHERE STUDENT_ID NOT IN (SELECT STUDENT_ID FROM USERS)
AND is_active_flg = 'Y';

END;
/

Hope, this will be useful.

Demo

answered Jun 3, 2019 at 11:55

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

fetch multiple rows, store in 1 variable and insert into table

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related