How to generate AUTOMATIC Number in Teradata SQL

auto-increment, sql, teradata

Solution

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)

Problem

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

Original source