CODEWITHSUNDEEP
sqloracle-database
Zhengyi Zhang
Zhengyi Zhang

Reputation: 63

how to multi-row insert in Oracle with sequence?

I want insert more than one row into table in Oracle . But it's confused me : I can not use 

select seq_table1.nextval  into table1 form dual

because the the table1 need be a new one. I need insert some rows into the exist table1.

And I also can not use 

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          (select seq_paper.nextval,'Multi 8000',1 from dual
 union all select seq_paper.nextval,'Multi 8001',1 from dual)

because oracle tell me that:

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the  
following constructs:  
■ A SELECT statement that is combined with another SELECT statement with the  
UNION, INTERSECT, or MINUS set operator ;
...and other constructs

Upvotes: 1

Views: 1579

Answers (2)

San
San

Reputation: 4538

Use nextval once and create data inside sub query:

SQL> CREATE TABLE pager (PAG_ID NUMBER,PAG_PARENT VARCHAR2(10), PAG_ACTIVE NUMBER);
Table created

SQL> CREATE SEQUENCE seq_paper START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE;
Sequence created

SQL> 

SQL> INSERT INTO pager
  2      (pag_id,
  3       pag_parent,
  4       pag_active)
  5      SELECT seq_paper.nextval,
  6             pag_parent,
  7             pag_active
  8        FROM (SELECT 'Multi 8000' pag_parent,
  9                     1 pag_active
 10                FROM dual
 11              UNION ALL
 12              SELECT 'Multi 8001',
 13                     1
 14                FROM dual);
2 rows inserted

SQL>

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33283

You can rephrase the select so that the nextval is not in a union.

The result would be something like:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE) 
    select seq_paper.nextval, NULL, a, b from 
        (select 'Multi 8000' a, 1 b from dual 
         union all 
         select seq_paper.nextval,'Multi 8001',1 from dual)

Upvotes: 2

Related Questions