Table Copy

[newerakb]

Here's my situation. I have a column with a CHAR(8) format that I need to change to a CHAR(10) format. Preventing a simple ALTER TABLE is that the field is the primary index. There is a lot of data in the table, so doing a CREATE from scratch followed by an UPDATE would be an intense query.

Our initial thought was to do a CREATE TABLE AS to just copy the data over, then drop the original and rename the new one. The problem with that, is that CREATE TABLE AS doesn't preserve properties such as NOT NULL or TITLE. I could do the CREATE TABLE AS and follow up with an ALTER to add those missing properties, but the 'create as' preserve the primary index, so we wouldn't be able to add NOT NULL or a TITLE to that field.

Is CREATE/UPDATE the only solution here? Is there some other way to simply change the format of a primary index that I'm missing? Any help would be appreciated.

[annal_tamizhnam]

You could run a Show table command to get the exact DDL, then change char(8) to char(10) and then
run the ddl script to create the table.

Then you can run an insert select command to insert data into the new table.

Since the PI of both the tables are same, the operation would be pretty fast.

Regards,
Annal T

[AndrewLivingsto]

Inserting data into an empty table is very quick, because Teradata does not have to refer to the transient journal.

Creating a new table with the datatype set correctly and then INSERT SELECTing into this new table from your original table is the way forward. Then DROP the original table and rename the new one to the old one.

It will be as though you never had the original table in the first place.

[Luckyhansh]

when insert into new table ,
it's just insert into index table, if not , index table need extra load

[newerakb]

Thanks for the help, all. I ended up going with the create-insert-drop-rename solution, which worked perfectly and quickly.

I'm still curious as to why CREATE TABLE AS does not preserve the NOT NULL and TITLE properties. Isn't the entire point of that query to create an exact copy of a table? And what good is a copy if it doesn't preserve column titles and field requirements?

[dnoeth]

"CREATE new_table AS existing_table" preserves all column attributes and indexes (just Triggers and Foreign Keys are removed), whereas "CREATE new_table AS (SELECT * FROM existing_table)" resets everything to defaults.

Dieter

[newerakb]

That makes sense. Just wish there was a way to use the first method and make simple changes.

Thanks!

[vasudev]

Hi Dieter,

When i create a big table using "create table as" what is the impact on the transient journal? Whether the TJ is loaded with all the data or its like insert select into the empty table which uses minimum TJ. Please advise.

Thanks in advance.