CODEWITHSUNDEEP
sqloracle-databaseplsql
AbdulRaheem
AbdulRaheem

Reputation: 115

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

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;
/

Upvotes: 0

Views: 178

Answers (2)

Popeye
Popeye

Reputation: 35900

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

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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;
/

Upvotes: 2

Related Questions