Create the table from another table using create table as select in Teradata
Contents
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 :
|
1 2 |
CREATE TABLE Database_name.Table_name_new AS (SELECT * FROM Database_name.Table_name_existing) with NO DATA; |
The below syntax without Select * will perform the same operation as above one.
|
1 2 |
CREATE TABLE Database_name.Table_name_new AS Database_name.Table_name_existing with NO DATA; |
Example :
|
1 2 |
CREATE TABLE Banking_DB.Customer_details AS (SELECT * FROM Banking_DB.Old_Customers) with NO DATA; |
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.
Syntax:
|
1 2 |
CREATE TABLE Database_name.Table_name_new AS (SELECT * FROM Database_name.Table_name_existing) with DATA AND STATS; |
Example :
|
1 2 |
CREATE TABLE Banking_DB.Transaction_new AS (SELECT * FROM Banking_DB.Old_Transaction) with DATA AND STATS; |
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.
Example
|
1 2 |
CREATE TABLE Banking_DB.Transaction_new AS Banking_DB.Old_Transaction with DATA AND STATS; |
Recommended Articles
- How to check the table definition or view definition of table in Teradata?
- How to create a view for a table in Teradata?
- Grant Select access to table in Teradata
