Sequence Generator

Database
Enthusiast

Sequence Generator

Hello TD Guru's,

I am trying to create a seq generaor in to an existing table a below, but without any luck, can someone please help me with the SQL code

INSERT INTO DB.TEST

Seq_No Integer GENERATED ALWAYS AS IDENTITY

(Start with 1

Increment By 1

MinValue 1

Maxvalue XXXXX

);

Thanks in advance.

5 REPLIES
Teradata Employee

Re: Sequence Generator

The format you have used .... is for defining a column in a table, like below:

CREATE TABLE TABLE1

(

Col1 INTEGER GENERATED ALWAYS AS IDENTITY

Start with 1

Increment By 1

MinValue 1

Maxvalue  10000

,Col2 INTEGER

)

Whereas .... if you have a simple INTEGER column and you wish to insert sequence .... you can use:

SELECT

CSUM(1,1)

<other columns>

FROM <Table>

Enthusiast

Re: Sequence Generator

Hello Adeel,

I already have the table, what i am after is adding a SKey using an Insert or an Update statement.

Teradata Employee

Re: Sequence Generator

You can visit the following thread, it has several options listed:

http://forums.teradata.com/forum/database/generate-surrogatekey-with-a-huge-table#comment-129849

How much data you will have to generate SK on?

Enthusiast

Re: Sequence Generator

Adeel, i did understand the theory behind it but i am having difficulties getting my SQL to work as am new to this kind of stuff..

I have an existing table called DB.Test and my intension is to generate a new column called Seq_ID.

So my first bit is adding filed to the table and the SQL as below

ALter Table DB.Test

Add Seq_No INTEGER/Decimal;

And now i am trying to get the Seq No's in to that newly added column, can you please provide a sample code i am not bothered wether its CSUM or IDENTITY, Can you please help

Teradata Employee

Re: Sequence Generator

Most easiest way would be as follows:

CREATE TABLE tab1
(
Name VARCHAR(10)
);

INSERT tab1 VALUES ('a');
INSERT tab1 VALUES ('b');
INSERT tab1 VALUES ('c');

CREATE TABLE tab2
(
ID INTEGER,
Name VARCHAR(10)
);

INSERT tab2
SELECT
CSUM(1,1),
Name
FROM Tab1;

HTH!