Archives of the TeradataForum

Message Posted: Tue, 15 Oct 2013 @ 21:47:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am not aware of any easy was outside of the CREATE TABLE AS...

It's very doable, but you will need to script the index portion.

For NUPI

     CREATE TABLE MY_NEW_TABLE as MY_EXISTING_TABLE WITH DATA AND STATS
     PRIMARY INDEX  (Col1....);

For UPI

     CREATE TABLE MY_NEW_TABLE as MY_EXISTING_TABLE WITH DATA AND STATS
     UNIQUE PRIMARY INDEX  (Col1....);

Other method:

1) Capture your DDL using show table commands (script it)... export them into single file to edit.

2) Capture STATS on existing tables via TSET tool.

3) create new tables with edited primary index names in different (new) database (with space to capture the data)

4) insert/select from old tables to new ones

5) Import STATS via TSET tool to new tables changing the target databasename on the import (saves time/resources recollecting stats)

6) delete/drop all the old tables (or delete database if you can to them all at once)

7) create table .tablename as .tablename with data and stats (do for all tables)

8) delete/drop all the tables in the

Kyle Prescott
DBA Manager
Unum-GHDS
Chattanooga, TN

	<Prev	Next>		<<First	<Prev	Next>	Last>>