CREATE TABLE AS (Time Series Form) Syntax | Teradata Vantage - CREATE TABLE AS Syntax (Time Series Form) - Analytics Database - Teradata Vantage

For a complete description of the CREATE TABLE statement and AS clause, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

{ CREATE | CT } [ table_kind ] TABLE target_table_name
  [, create_table_options ]
  ( [ generated_columns, ] column_definitions )
  AS { source_table_name | subquery } WITH [NO] DATA
  [ PRIMARY TIME INDEX [ index_name ] (
      timecode_data_type
      [, timezero_date ]
      [, timebucket_duration ]
      [, COLUMNS ( column_list ) ]
      [, { NONSEQUENCED | SEQUENCED [ ( seq_max ) ] } ]
    ) [ secondary_index_definitions ] [ commit_options ]
  ] [;]

Syntax Elements

target_table_name

The name of the target table, optionally prefaced by a database or user name, that CREATE TABLE AS creates.

create_table_options

All standard CREATE TABLE options for non-PTI tables apply to PTI tables. Syntax for these options is described in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

generated_columns

These columns are generated automatically by Vantage. They are described in Automatically Generated Columns in PTI Tables.

column_definitions

Standard column definitions for the time series data that will be stored in the PTI table. For more information about standard column definitions in CREATE TABLE statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

source_table_name

The name of the source table, optionally prefaced by a database or user name. The source table can be a PTI or non-PTI table.

subquery

A SELECT statement that explicitly chooses a subset of columns from the source table to be reproduced in the target table. If you use a subquery, you must also use the WITH DATA option.

In order to create a PTI target table from a non-PTI source table, or create a non-PTI table from a PTI table, you must use a subquery, even if the source and target tables will have the same columns.

For more information about subqueries in CREATE TABLE AS statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

WITH [NO] DATA

Determines whether the target table is populated with data from the source table or subquery, or exists as an defined but empty table.

PRIMARY TIME INDEX

• If you specify the PRIMARY TIME INDEX clause, the target table will be a PTI table, regardless of the source table type.
• If you do not specify the PRIMARY TIME INDEX clause, the target table can be either a PTI table or a non-PTI table, depending on the source table type.

All elements of the PRIMARY TIME INDEX clause are the same as those described for CREATE TABLE (Time Series Form), and the consequences to the automatically generated columns are the same.