CODEWITHSUNDEEP
sqloracle-databaseloopsplsqlinsert
Gunnlaugur
Gunnlaugur

Reputation: 341

How to use for loop for insert in Oracle?

I'm having trouble inserting 1 million records in Oracle using the following for loop. The insert statement works on it's own but not inside the loop. What am I doing wrong here?

BEGIN
    FOR v_LoopCounter IN 385000000..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');
        COMMIT;
    END LOOP;
END;

Upvotes: 5

Views: 51333

Answers (4)

Satish Ingle
Satish Ingle

Reputation: 41

This worked for me:

BEGIN
FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');

END LOOP;
COMMIT;
END;

Upvotes: 4

Littlefoot
Littlefoot

Reputation: 142705

Don't do it that way, especially don't COMMIT within a loop.

Use a row generator; there are many techniques, one of them being this one:

SQL> create table test (snb number, real_exch varchar2(20));

Table created.

SQL> insert into test (snb, real_exch)
  2    select 385000000 + level - 1, 'GSMB'
  3    from dual
  4    connect by level <= 10;        --> you'd put a million here

10 rows created.

SQL> select * from test;

       SNB REAL_EXCH
---------- --------------------
 385000000 GSMB
 385000001 GSMB
 385000002 GSMB
 385000003 GSMB
 385000004 GSMB
 385000005 GSMB
 385000006 GSMB
 385000007 GSMB
 385000008 GSMB
 385000009 GSMB

10 rows selected.

SQL>

Upvotes: 8

George Joseph
George Joseph

Reputation: 5922

You dont need to use a loop to insert data like this. Try using straight SQL which will get you what you want in an elegant manner

 INSERT 
   INTO PORT (snb
              , real_exch
              , act_exch
              , user_type
              , status_id
              , category_id
              , assignable
             ) 
    select * 
      from (
            SELECT row_number() over(order by 1) + 385000000 -1 as loop_val
                   ,'GSMB'
                   ,'GSMB'
                   ,'GSM'
                   ,0
                   ,90
                   ,'0'
               FROM (select level as lvl
                       from dual
                     connect by level<=1000)a
               JOIN (select level as lvl
                       from dual
                     connect by level<=1000)b
                 ON 1=1  
            )x
       where x.loop_val<=385999999;

commit;

Upvotes: 1

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

Try:

BEGIN
    FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');
        COMMIT;
END LOOP;

Upvotes: -2

Related Questions