Copying Table Definitions | CREATE TABLE ... AS | Teradata Vantage - 17.10 - About Copying Table Definitions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

You can create work or test tables that are identical to, or based on, base tables in a database. For example, you can use work tables with the same structure as base tables to speed up processing because it is faster to insert rows into a unpopulated work table and later merge the updates into a persistent base table than it is to update the base table directly one update at a time.

You can create test tables that are identical to some or all of the base tables used by live applications for prototyping new applications on a test system.

The AS clause option of CREATE TABLE provides a simple method for copying some or all definitions from an existing table to a new table.

The AS … WITH NO DATA clause copies only the source table or query expression definitions to the new table, while the AS … WITH DATA clause copies both the source table definitions and its data to the new table.

You can also append an AND STATISTICS option to a WITH [NO] DATA clause to copy any collected source table statistics or empty histograms to a target table. If you specify WITH NO DATA AND STATISTICS, the system sets up the appropriate statistical histograms in the dictionary, but does not populate them with source table statistics. This is referred to as zeroed statistics.