CREATE TABLE AS Statement | Teradata Database - Example: CREATE TABLE AS, CT AS, and Table Kind - Teradata Database

If you use the abbreviated CT AS syntax rather than the full CREATE TABLE AS syntax when creating a copied target table definition, you cannot specify a table kind. Because of this limitation, the system automatically defaults the definition of the target table kind to the table kind of the source table in the operation, as the following example illustrates.

Assume you are running in Teradata session mode, where the default table kind is SET.

First you create the source table whose definition and data are to be copied to a new table under a different name.

CREATE MULTISET TABLE test_m (
  i INTEGER) 
PRIMARY INDEX(i);

Run SHOW TABLE on test_m to report its definition as stored by the system:

SHOW TABLE test_m;

The complete table definition reported by SHOW TABLE, which includes defaults for journaling and checksums, is as follows.

CREATE MULTISET TABLE ARUN.test_m ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      i INTEGER)
PRIMARY INDEX ( i );

Now copy the definition for test_m and its data to a new table, test_m2:

CREATE TABLE test_m2 AS test_m WITH DATA;

Because you used the CREATE TABLE AS syntax, you were unable to specify the MULTISET table kind.

Now run SHOW TABLE on test_m2 to report its definition as stored by the system:

SHOW TABLE test_m2;

The result is as follows:

CREATE MULTISET TABLE ARUN.test_m2 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      i INTEGER)
PRIMARY INDEX ( i );

Because the SHOW TABLE reports on both tables indicate their table kind is MULTISET, you can see that the default table kind was copied from the definition of test_m to the definition of test_m2.