Copy a Table | VantageCloud Lake - Copying a Table

Copy a Table | VantageCloud Lake - Copying a Table - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

ft:locale

en-US

ft:lastEdition

2024-12-11

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Use the AS option of the CREATE TABLE statement to copy part or all of an existing table. The following table describes 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 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 may 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 to populate the table 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 may not be appropriate. Teradata highly recommends specifying explicitly an appropriate PI, Primary AMP index, or specify NO PRIMARY INDEX.

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 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 may 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 to populate the table 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 may not be appropriate. Teradata highly recommends specifying explicitly an appropriate PI, Primary AMP index, or specify NO PRIMARY INDEX.