Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Inserting an auto number into a table?
- Thread starter bozga
- Start date
ANSI SQL has something called sequence generators.
[tt]
create sequence s no cycle;
create table t (id integer default next value for s, c1 character(10));
[/tt]
Works fine when inserting one row at the time:[tt]
SQL>insert into t (c1) values ('1:st');
SQL>insert into t (c1) values ('2:nd');
SQL>select * from t;
ID C1
=========== ==========
1 1:st
2 2:nd
[/tt]
But does not work when inserting several rows with one insert:[tt]
insert into t(c1) select somecolumn from sometable;
[/tt]
In this case the id column will have the same value for all inserted rows. (The "next value for s" is only performed once for a statement.)
[tt]
create sequence s no cycle;
create table t (id integer default next value for s, c1 character(10));
[/tt]
Works fine when inserting one row at the time:[tt]
SQL>insert into t (c1) values ('1:st');
SQL>insert into t (c1) values ('2:nd');
SQL>select * from t;
ID C1
=========== ==========
1 1:st
2 2:nd
[/tt]
But does not work when inserting several rows with one insert:[tt]
insert into t(c1) select somecolumn from sometable;
[/tt]
In this case the id column will have the same value for all inserted rows. (The "next value for s" is only performed once for a statement.)
- Status