CREATE TABLE … AS Statement | Teradata Vantage - Example: Comparing CREATE TABLE … AS and CT … AS in Regard to Table Kind - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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, Vantage 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.