CODEWITHSUNDEEP
sqloracle-database
Arne
Arne

Reputation: 2674

Insert row with increasing numerical ID (e.g. rownum) using Oracle SQL?

I would like to insert new rows using Oracle SQL into a table, where one field simply needs to be unique, e.g. simply the rownum value. However this statement does not work:

insert into MYTABLE ( ID_, INDEX_, TYPE_, SOMEVALUE_  ) values ( "FOOBAR", rownum, 1, 999 );

This will result in an error message:

00976. 00000 -  "Specified pseudocolumn or operator not allowed here."
*Cause:    LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or
           CONNECT_BY_ISCYCLE was specified at an illegal location.

I am open for an efficient alternate solution, if using rownum is not possible.

Upvotes: 0

Views: 2300

Answers (1)

Sameer Mirji
Sameer Mirji

Reputation: 2245

What you need here is to create a SEQUENCE.

Follow the SEQUENCE creation and usage document here.

For instance you can create SEQUENCE like:

CREATE SEQUENCE mytable_sq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

Then you can do this:

insert into MYTABLE ( ID_, INDEX_, TYPE_, SOMEVALUE_  ) values ( "FOOBAR", mytable_sq.nextval, 1, 999 );

Upvotes: 3

Related Questions