16.10 - Copying a Table - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Use the AS option of the CREATE TABLE statement to copy some or all of an existing table. The following table describes several options for copying tables.

Statement Result
CREATE GLOBAL TEMPORARY TABLE AS [tablename/query_expression] …WITH NO DATA Copies an existing table as a global temporary table. Use WITH NO DATA, because global temporary tables are not populated until they are materialized by being referenced in a query.

If you want the table to inherit the following:

  • All of the column definitions, then specify tablename WITH NO DATA.
  • A subset of the column definitions, then specify (query_expression) and WITH NO DATA.

The subquery form of CREATE GLOBAL TEMPORARY TABLE AS...WITH NO DATA does not copy indexes and defines a default primary index which might not be appropriate. Teradata highly recommends specifying explicitly an appropriate PI or specify NO PRIMARY INDEX.

CREATE TABLE AS [tablename/query_expression] WITH [NO] DATA [AND STATISTICS] Copies an existing table as a permanent table or a volatile table. You choose what columns you want to copy and whether the table should be populated automatically, as follows. If you want the table to inherit the following:
  • All of the column definitions plus the contents, then specify tablename WITH DATA and (for a volatile table) ON COMMIT PRESERVE ROWS.
  • All of the column definitions but none of the contents, then specify tablename WITH NO DATA.
  • A subset of the column definitions plus the contents, then specify (query_expression) WITH DATA and (for a volatile table) ON COMMIT PRESERVE ROWS.
  • A subset of the column definitions but none of the contents, then specify (query_expression) WITH NO DATA.
  • The statistical histograms of the original table, then specify AND STATISTICS. If USECOUNT is enabled, this option also copies use count information from the original table. See SQL Logging Statements.

The subquery form of CREATE TABLE AS...WITH [NO] DATA does not copy indexes or partitioning and defines a default primary index, which might not be appropriate. Teradata highly recommends specifying explicitly an appropriate PI, Primary AMP index, or specify NO PRIMARY INDEX.