All Forums Database
taruntrehan 43 posts Joined 10/12
20 Feb 2013
Command Help : Alter table modify column

Hi All,
 
I have an existing table in TERADATA and it has a field named log_dttm defined as timestamp.
I want to change the column data type to varchar(30).
"alter table modify" failed to convert.
 ALTER TABLE Failed. 3558:  Cannot alter the specified attribute(s) for log_dttm. 
Is there any direct command to accomplish this?
 

Regards, Tarun Trehan http://allzhere.in
Qaisar Kiani 337 posts Joined 11/05
20 Feb 2013

ALTER TABLE to change the datatype of existing columns is very limited. You can use this statement to change the VARCHAR and increase the number of characters but I don't think you can do that to change TIMESTAMP to VARCHAR.
You can add a column and insert the data values in it while casting to VARHCAR and later on rename the column if required!

taruntrehan 43 posts Joined 10/12
20 Feb 2013

Thanks for your inputs ...
I am aware of this approach.
However, wanted some inputs as there are 25 columns to be renamed !!!
Further, i need to maintain the same order of columns as there is load process enabled to load a "|" separated file.
 

Regards,
Tarun Trehan
http://allzhere.in

KS42982 137 posts Joined 12/12
20 Feb 2013

As you need to rename so many columns, I'd suggest, you create a new table with desired datatypes, copy all the data from the existing table to this new table by casting to varchar, drop the existing table and re-create with new datatypes and insert the records from the temp table to this newly create table and finally drop the temp table. It would be a much cleaner and safer approach for such kind of operations.

dnoeth 4628 posts Joined 11/04
20 Feb 2013

@KS42982:
Why use a temp table and drop it
Create a new table with the required definition, insert/select and the rename the tables:
create newtab (...);
ins newtab sel * from oldtab;
collect stats on newtab from oldtab; -- only for those columns where the datatype didn't change
drop table oldtab;
rename newtab to oldtab;
Dieter
 

Dieter

KS42982 137 posts Joined 12/12
20 Feb 2013

That is definately a good approcah. However, what I hear from DBAs in my company that RENAMEing table is not a good stable thing to do in teradata. So in such kind of situations, they always prefer us to do using temp table.

dnoeth 4628 posts Joined 11/04
20 Feb 2013

I never heard of problems regarding RENAME.
It just needs a short exclusive lock, but you probably don't want to do that during production hours anyway.
Dieter

Dieter

taruntrehan 43 posts Joined 10/12
21 Feb 2013

Hi All,
Here DBA team has a different standardised approach as per them.
They ask you to create new columns, copy data , drop old column , rename new columns.
Its a burden on developer as to change datatype of one column, i have to play with all columns in order to maintain the same order; else from load process will fail .. :(

Regards,
Tarun Trehan
http://allzhere.in

asadali.khan 11 posts Joined 10/10
26 Feb 2013

Pretty Simple.
ADD command would be used to alter the datatype of the existing field.
 
ALTER TABLE <DatabaseName>.<TableName>
ADD <ExistingColumnName> <New Data Type>
;
 
Br,
Asad

Asad Ali Khan

deva 2 posts Joined 05/13
08 May 2013

how can i change  the data in the existing table 

REGARDS

DEV

Shelley 28 posts Joined 09/10
09 May 2013

Depending on what exactly you are trying to do, you can run an update to change values in the table. To change column format I would do an insert select from the old table into a new table with the new format, assuming of course they are compatible

You must sign in to leave a comment.