Archives of the TeradataForum

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

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

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>>