Creating a table in Teradata can be done in multiple ways. In this post, we will see how to create a table from another table without the select statement. The following examples will show how to create a table from another table in Teradata.
Creating on the fly with no data
This is the case for creating a table in Teradata using another table name
Syntax
CREATE TABLE DATABASE.TABLEA as DATABASE.TABLEB WITH NO DATA;
Yes, the select statement from the source table is missing but still, the target table will be created with the structure of the source table.
Let us see an example with this syntax
Create table testdb.newtesttable as testdb.oldtesttable with no data;
In this example, a new table “newtesttable” will be created with the same structure as the “oldtesttable”. However, the data from the source table will not get copied to the target table as we have given the “with no data” option at the end of the create table statement.
Creating a table on the fly with data
This is the case for creating a table in Teradata with data. The data will be available as soon as the statement is executed.
Syntax
CREATE TABLE DATABASE.TABLEA as DATABASE.TABLEB WITH DATA;
This method of creating a table will copy the data from the source table to the new target table that is getting created.
Let us see an example using this syntax
Create table testdb.newtesttable as testdb.oldtesttable with data;
In this example, the new target table “newtesttable” will be created from the source table “oldtesttable”. During the table creation, the data from the source table will also get copied to the target table as we have given the option “with data” at the end of the create table statement.
Creating a table on the fly with index but not data
Syntax
CREATE TABLE DATABASE.TABLEA as DATABASE.TABLEB WITH NO DATA PRIMARY INDEX(colname);
This method will create the target table with the structure of the source table without any data being copied. It will also create the “primary index” for the specified column
Let us see an example using this syntax
Create table testdb.newtesttable as testdb.oldtesttable with no data primary index(custid);
In this example, the target table “newtesttable” will get created with the structure of the “oldtesttable”. But, since, we have given the option “with no data”, the table will only be created as empty. Also, the primary index will be created on one of the columns in the table.
Creating a table on the fly with index and data
Syntax
CREATE TABLE DATABASE.TABLEA as DATABASE.TABLEB WITH DATA PRIMARY INDEX(colname);
This will help us to create the target table with both the index and data.
Let us see an example using this syntax
Create table testdb.newtesttable as testdb.oldtesttable with data primary index(custid);
In this example, the target table “newtesttable” will get created with the structure of the “oldtesttable”. As we have given the option “with data” in the statement, the data from the source table will also get copied to the target table. Also, as specified, the primary index will be created on the “custid” column in the table.
Also check,