Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 15 Dec 2008 @ 22:31:20 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: What is the alternative way to sequence
From:   McCall, Glenn David

You have a couple of options.

First is to use an identity column.

     Create table test (
           Col_name integer generated always as identity,

Note that these numbers will not be sequential - without going into detail it is because of the parallelism of the system.

The other approach is to maintain a separate table with the next ID value.

     Insert into some_table (...)
     Select IDs.nextvalue, ...
      from IDS cross join  /* Rest of query goes here */
     ; update IDS
        Set nextvalue = nextvalue + 1;

Important: Note the positioning of the semi-colons - especially if you are doing this with bteq.

The above assumes that IDS has a single row in it. You could use the IDS table for multiple tables if you added a key to it (i.e. the columns would be (tableid (UPI), nextvalue) or something like that).

Hope this helps

Glenn Mc

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020