All Forums Database
NewAmigo 27 posts Joined 02/14
19 Feb 2014
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.

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

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>
 

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
20 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

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?

-- If you are stuck at something .... consider it an opportunity to think anew.

NewAmigo 27 posts Joined 02/14
20 Feb 2014

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

Adeel Chaudhry 773 posts Joined 04/08
20 Feb 2014

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!
 

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.