How to generate AUTOMATIC Number in Teradata SQL

Question

I want to generate AUTOMATIC Number to use TD SQL, for example as follows,

CREATE MULTISET TABLE TEST_TABLE
(
  AUTO_NUMBER INT,
  NAME VARCHAR(10)
)
PRIMARY INDEX (AUTO_NUMBER);

INSERT INTO TEST_TABLE
VALUES('TOM');
INSERT INTO TEST_TABLE
VALUES('JIM');
INSERT INTO TEST_TABLE
VALUES('JAN');

SELECT * FROM TEST_TABLE;

The result above will be ,

1 TOM
2 JIM
3 JAN

In this way you can add an autoincrement column: http://forums.teradata.com/forum/enterprise/auto-increment-column — Joe Taras, Feb 24 '14 at 08:16
Do you actually need a sequence without gaps? 1,2,3? IDENTITY doesn not guarantee sequencial numbers, in fact it's not a single sequence but multiple sequences for each AMP/PE. — dnoeth, Feb 24 '14 at 17:20

score 7 · Accepted Answer · answered Feb 24 '14 at 08:16

7

Create a column with the below syntax:

SEQ_NUM decimal(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 2147483647 
            NO CYCLE)

answered Feb 24 '14 at 08:16

Raj

22,346
14
99
142

will this work on a preexisting table with already populated rows? – tiffylou Nov 15 '16 at 16:02

score -1 · Answer 2 · answered Aug 29 '15 at 20:17

Usually there is a column in the table which is unique.

You can use below technique to add a column in your result set if you dont want to add extra column to your table.

select RANK() OVER ( ORDER BY ),T.* SEQ from TABLE T;

It will give you output like:

1 a xx yy 2 b xx yy 3 c xx yy

How to generate AUTOMATIC Number in Teradata SQL

2 Answers2