CREATE TABLE to Copy an existing table Teradata

Confucius taught us "A journey of a thousand miles begins with a single step." The journey of the DBA copying a table used to be inefficient and contain too many steps. Teradata V2R4, has taken on the challenge, stepped up to the plate and scored a home run. In Teradata V2R4 and beyond, there is a new format that does it all in one easy command.

It has been modified to create a new table from the definition of an existing table. The only difference between the tables is the table-name. All columns have the same names, data types and constraints. All indices and table options are also identical.

The new syntax is:

Notice the specification of WITH. It is required and requests either the DATA or NO DATA specification. When DATA is requested, not only does the system create the new table, but it also copies all of the rows and columns from the original table into the new table. The NO DATA specification means that the original data rows are not to be copied into the new table. It remains empty of rows.

This is nice because it is now easier than using the SHOW TABLE to get the DDL, copying the DDL, changing the table-name and then submitting the DDL. Plus, if you do want the data rows, there is no need to perform a separate INSERT/SELECT. Optionally, another database or user area may be used to qualify the names of the two tables. Creating and duplicating tables has never been easier! In addition, the "existing" table could be a derived table, but you probably want to specify which column or columns to use as the primary index.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics