All Forums Database
newerakb 14 posts Joined 12/07
21 Dec 2007
Table Copy

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.

24 Dec 2007

You could run a Show table command to get the exact DDL, then change char(8) to char(10) and thenrun 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

24 Dec 2007

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.

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080

Luckyhansh 30 posts Joined 08/06
25 Dec 2007

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

newerakb 14 posts Joined 12/07
27 Dec 2007

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 4628 posts Joined 11/04
27 Dec 2007

"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 14 posts Joined 12/07
27 Dec 2007

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

vasudev 24 posts Joined 12/12
16 Nov 2013

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. 

You must sign in to leave a comment.