How to Add IDENTITY_COLUMN into existing table ?

Database

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.

10 REPLIES

Re: How to Add IDENTITY_COLUMN into existing table ?

Identity column cannot be added to an existing table..

Re: How to Add IDENTITY_COLUMN into existing table ?

DO I have any other option to resolve my issue?

Re: How to Add IDENTITY_COLUMN into existing table ?

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

Re: How to Add IDENTITY_COLUMN into existing table ?

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

Teradata Employee

Re: How to Add IDENTITY_COLUMN into existing table ?

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;

Re: How to Add IDENTITY_COLUMN into existing table ?

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

Re: How to Add IDENTITY_COLUMN into existing table ?

@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?

Re: How to Add IDENTITY_COLUMN into existing table ?

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

N/A

Re: How to Add IDENTITY_COLUMN into existing table ?

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).