CREATE TABLE using SELECT in Teradata

In the other post, we saw how to create tables in Teradata and the corresponding examples for each. Here we will see how to create a table with select from another table. In this post, we will see how we can create a table using select in Teradata. There are different ways in which we can create a table in Teradata using the select query.

Create table using select (without data and index)

Syntax

CREATE [SET/MULTISET/BLANK] TABLE DBTEST.TESTTABLE AS
(
SELECT COL1, COL2, COL3 
FROM
DBNAME.TABLENAME
)WITH NO DATA
[BLANK/NO INDEX];

In the above example, please note the word “WITH NO DATA” after the ‘)’. This will create a new table with the structure the same as the select statement without copying the data.

Let us see a real-time example for the above syntax. The below code will create an empty table ‘dim_channel’ in the ‘testdb’. The structure of the table will be as of the Select statement given inside the brackets. In this case, we are only creating an empty table with no index. Instead of column names, you can also use “select *”

create table tesdtdb.dim_channel as
(
select 
chnl_key, chnl_nm, chnl_type 
from prod.dim_channel
)with no data
;

Create table using select without data with index

Similar to the above method, we can create a table from another table with the index also. You can specify the primary index.

Syntax

CREATE [SET/MULTISET/BLANK] TABLE DBTEST.TESTTABLE AS
(
SELECT COL1, COL2, COL3 
FROM
DBNAME.TABLENAME
)WITH NO DATA
PRIMARY INDEX(index col name);

In the above syntax, you can see the inclusion of ‘PRIMARY INDEX’ at the end of the statement. This will create the new table along with the index column.

Let us see an example for the create table using select along with index but without data. The below code will create the ‘dim_channel’ table under ‘testdb’ without any data. But the table will have a primary index on the ‘chnl_key’ column.

create table tesdtdb.dim_channel as
(
select 
chnl_key, chnl_nm, chnl_type 
from prod.dim_channel
)with no data
primary index(chnl_key);

If you do not give any option like SET or MULTISET, it will take MULTISET by default. So when you try to create a SET table with no index, it will throw the below error.

Executed as Single statement.  Failed [3706 : 42000] Syntax error: Cannot create a NoPI table as a SET table.

Creating table using select with data but no index

While creating the table if you want to load the data also, you can use the “WITH DATA” option at the end of the statement.

Let us see an example for the case where you want to create a table on the fly using a select statement along with the data.

create table tesdtdb.dim_channel as
(
select 
chnl_key, chnl_nm, chnl_type 
from prod.dim_channel
)with data;

The above code will create the table ‘dim_channel’ along with the data from the production table. Here, since we did not specify any index option, there won’t be any index created.

Create table using select with data and index

There are cases when you create a table on the fly along with the index for the proper distribution of the data. You can use ‘PRIMARY’ or ‘UNIQUE PRIMARY INDEX’ for that matter. This can be combined with the ‘WITH DATA’ option so that the target table will be created along with the data and index.

create table tesdtdb.dim_channel as
(
select 
chnl_key, chnl_nm, chnl_type 
from prod.dim_channel
)with data
primary index(chnl_key);

The point here is if you want to create a SET table, you have to create an index. Otherwise, it will throw the error.

Also check “Creating tables in Teradata“, “Creating table in Teradata using another table“