17.05 - CREATE TABLE ... AS and Temporal Tables - Teradata Database

Teradata Vantage™ - Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1182-170K
CREATE TABLE ... AS, the syntax for copying all or a portion of a table, can be used to create a temporal or nontemporal table from a source temporal table. The syntax for CREATE TABLE ... AS is identical, whether used with a temporal or nontemporal source table. The syntax is described fully in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. However, note the following information, which is specific to using a temporal source table.
  • If an AS subquery of a temporal table is used without a temporal qualifier the default temporal qualifier set for the session is used for the subquery.
  • New columns defined for a table in the CREATE TABLE ... AS statement cannot be VALIDTIME or TRANSACTIONTIME columns.
  • If the source table is a temporal table with a valid-time column, the target table created is a temporal table that has a valid-time column. In this case the following apply:
    • If WITH DATA is specified in the CREATE TABLE ... AS statement, the valid-time column values are copied to the target table.
    • Target tables can be created with column- and table-level temporal constraints. For tables with valid time, primary key and unique constraints automatically create system-defined join indexes (SJIs). These types of constraints can be defined on target tables in CREATE TABLE ... AS statements only if the WITH NO DATA option is used. (Note that NONSEQUENCED VALIDTIME primary key and unique constraints act as for nontemporal table, and create unique secondary indexes, rather than SJIs.)
  • If the source table is a temporal table with a transaction-time column, the target table created is a temporal table that has a transaction-time column. In this case, the following apply:
    • The transaction-time values are copied to the target table only if the NONTEMPORAL qualifier is used with CREATE TABLE ... AS. (Note that the NONTEMPORAL privilege is required to use the NONTEMPORAL qualifier.) The NONTEMPORAL qualifier is ignored if the source table does not have a transaction-time column.
    • If the NONTEMPORAL qualifier is not used, the transaction-time values in the target table default to (TEMPORAL_TIMESTAMP,UNTIL_CLOSED).
    • Target tables can be created with column- and table-level temporal constraints. These constraints are applicable only to open rows. Target tables with transaction-time can only have primary key and unique constraints, if the WITH NO DATA option is used.