15.10 - AS Clause (Copy Table Syntax) - Teradata Database

You cannot specify data types for source table columns, but you can specify column attributes for them. You can also specify table constraints for the target table to be created.

You cannot specify the NORMALIZE option for a target table. Otherwise, the system returns an error to the requestor.

Example: Equivalence of CREATE TABLE … AS and CT … AS With Respect to Table Kind

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, Teradata Database 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.