Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    26

    Unanswered: Auto-increment in a temporary table

    Hi all,

    Is it possible to create a temporary table with auto-increment feature? For example in db2 i would have:

    declare global temporary table abc
    (id_number integer generated always as identity(start with 1,increment by1),
    branch_num char(6) )
    on commit preserve rows

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, in Oracle you use a SEQUENCE to generate surrogate keys like this:

    Code:
    SQL> create global temporary table abc
      2  (id_number integer ,
      3  branch_num char(6) )
      4  on commit preserve rows;
    
    Table created.
    
    SQL> create sequence abc_seq;
    
    Sequence created.
    
    SQL> insert into abc (id_number, branch_num) values (abc_seq.nextval, 'x');
    
    1 row created.
    
    SQL> insert into abc (id_number, branch_num) values (abc_seq.nextval, 'y');
    
    1 row created.
    
    SQL> select * from abc;
    
     ID_NUMBER BRANCH
    ---------- ------
             1 x
             2 y
    Read more about sequences here.

  3. #3
    Join Date
    Apr 2007
    Posts
    26
    Thanks alot, that was very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •