CODEWITHSUNDEEP
databaseteradata
Pugazh
Pugazh

Reputation: 9561

Difference between create table syntax in teradata

I'm unable to figure out the difference between below teradata syntax. Could someone please help.

CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH DATA;

vs

CREATE TABLE EMP_TABLE_BACKUP AS (SELECT * FROM EMP_TABLE ) WITH DATA;

Upvotes: 0

Views: 2971

Answers (1)

dnoeth
dnoeth

Reputation: 60482

There are huge differences between CREATE TABLE AS existing_table and CREATE TABLE AS (SELECT...):

When you copy an existing table most of the attributes on table & column level are inherited, e.g. SET or MULTISET, Primary & Secondary Indexes, NOT NULL, COMPRESS. Only Triggers & Foreign Keys are not copied.

But when you materialize a SELECT, most attributes are lost, e.g. every column will be NULLable & and the PI probably defaults to the 1st column.

You get all details in the Teradata manuals

Upvotes: 3

Related Questions