All Forums Database
Vivek79 13 posts Joined 04/15
07 Apr 2016
How to Add IDENTITY_COLUMN into existing table ?

Friends,
I'm working in Data Masking Company. Now I'm developing Teradata Scramble.
I've a situation here to scramble a table having more than 10 Lakhs to 10 Cores of records, but due to some performance issues I'm planing to split the records into many phases depends on total records and do the scramble for each and every phase.
So i need to split the records for all the tables first, for that I need an IDENTITY_COLUMNS for all the tables. So, now i need to add an IDENTITY_COLUMN into existing table in teradata. like adding sequential number for that column (like 1,2,3,.,.,.,.,n).
 
Simple think, I need to add a identity column into existing table, update that column into sequential number.
Please help me to figureout this issue.
 
 
 
 

yuvaevergreen 93 posts Joined 07/09
07 Apr 2016

Identity column cannot be added to an existing table..

Vivek79 13 posts Joined 04/15
07 Apr 2016

DO I have any other option to resolve my issue?

yuvaevergreen 93 posts Joined 07/09
07 Apr 2016

Add a BIGINT column. Generate  surrogate keys  and update the column for each row in that table.
 
 

Vivek79 13 posts Joined 04/15
07 Apr 2016

@yuvaevergreen Can you guide me how to do clearly, please!

AtardecerR0j0 71 posts Joined 09/12
07 Apr 2016

I would do something like this:

/*
I suppose your starting point is EXAMPLE
*/
CREATE TABLE EXAMPLE
(
 COL1 CHAR(4)
)PRIMARY INDEX( COL1 );

INSERT INTO EXAMPLE (COL1) VALUES( 'AAAA' );
INSERT INTO EXAMPLE (COL1) VALUES( 'AAAB' );
INSERT INTO EXAMPLE (COL1) VALUES( 'AABB' );
INSERT INTO EXAMPLE (COL1) VALUES( 'BBBB' );


/*
Create a new EXAMPLE table
*/
CREATE MULTISET TABLE EXAMPLE_ID
(
 ID_PETICION INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 1 
            MAXVALUE 2147483647 
            NO CYCLE),
 COL1 CHAR(4)
)PRIMARY INDEX( COL1 );

INSERT INTO EXAMPLE_ID (COL1)  SELECT COL1 FROM EXAMPLE;

/*Swap the names*/
DROP TABLE EXAMPLE;
RENAME TABLE EXAMPLE_ID TO EXAMPLE;

SELECT * FROM EXAMPLE;

 

Be More!!

07 Apr 2016

I think the table can be altered
Add the column to the last in ddl,or last option is use a qualifier

Vivek79 13 posts Joined 04/15
07 Apr 2016

@AtardecerR0j0 Friend thank you so much for the reply,but you know its very hard to make a copy of 10Cores data to another table. So i already know this is not a right way friend.
So, are we having any other way to add identity column into existing table?
 

Vivek79 13 posts Joined 04/15
07 Apr 2016

@kumarvai...bhav1992 bro can you tell me clearly please!

dnoeth 4628 posts Joined 11/04
08 Apr 2016

There's no way to add an IDENTITY column to a populated table using ALTER TABLE.
 
And copying 100.000.000 rows to a new table isn't that hard on a Teradata system (of course you shouldn't change the PI). Best performance should provide a MERGE (instead of INSERT/SELECT).

Dieter

Vivek79 13 posts Joined 04/15
08 Apr 2016

@dnoeth I'm waiting for your reply seriously. Thanks for the reply.
Hope you remember,everytime i ask doubt's in teradata, you always shares the right answer.
So, I don't have any other option to achieve my requirement, except creating new table!!!
Right?
 
 
 
 

You must sign in to leave a comment.