Create the table from another table using create table as select in Teradata

Teradata provides the option to copy the existing table structure and create the new table.We can either copy the table structure alone or create the table with data.

Copy the table structure alone in Teradata:

We can copy the table structure from one database to another database using the query mentioned below. CREATE table as will not work if the source table has the referential integrity constraints or any columns defined as identity columns.

Syntax :

The below syntax without Select * will perform the same operation as above one.

Example :

Copy the table structure with data in Teradata:

Teradata maintains the statistics of each table such as how many distinct values,column stats,index stats and so on. If you want to copy the data along with statistics, you can use the WITH DATA AND STATS in the create table as select statement.


Example :

Create table from another table with out select statement

While we create the table from another table in Teradata, we can remove the Select statement from the Create table statement. We can just mention the old table name and the new table name as below, that will copy the data with the same structure.


Recommended Articles