CREATE TABLE AS Statement (ANSI Valid-Time Table Form) | Teradata Vantage - CREATE TABLE AS (ANSI Valid-Time Table Form) - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

Creates a new temporal table by copying all or a portion of the structure and contents of an existing temporal table.

System-time tables cannot be source tables for CREATE TABLE AS statements.

Syntax

There is no special additional temporal syntax for the CREATE TABLE AS statement. The syntax is identical to that used for nontemporal tables. That syntax is described fully in Teradata Vantageā„¢ - SQL Data Definition Language Syntax and Examples, B035-1144. But note the restrictions listed in the following Usage Notes.

Usage Notes for ANSI Valid-Time Tables

  • In order to copy the derived VALIDTIME period definition to the new table, you must use the table-copy form: CREATE TABLE target_table AS source_table rather than CREATE TABLE target_table AS (subquery).
  • If you specify new column names in the table-copy form of a CREATE TABLE AS statement, you must specify a name for the valid-time derived period column, which is also copied from the source valid-time table.
  • You cannot, and need not, explicitly specify the [AS] VALIDTIME column attribute in a CREATE TABLE AS statement, even if you specify new column names for the target table.
  • 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 valid-time tables having primary key and unique constraints, the database automatically creates 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 tables, and create unique secondary indexes, rather than SJIs.)