Copy data from foreign table to table | Teradata Vantage - 17.10 - Example: Copying Data from Foreign Table into Permanent Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

This example uses CREATE TABLE AS … WITH DATA to copy data from a foreign table into a permanent table without a primary index.

The foreign table is riverflow_0627, created in Example: Copying Foreign Table without Data.

CREATE TABLE AS ... WITH DATA Statement for Permanent Table

CREATE TABLE riverdata_0627_perm 
  AS (SELECT location, payload FROM riverflow_0627) 
  WITH DATA
  NO PRIMARY INDEX;

The SELECT subquery selects the location and payload columns from riverflow_0627, including the data. Internally, the subquery performs an INSERT ... SELECT operation with the HASH BY RANDOM clause to provide even distribution of data. HASH BY RANDOM is the default for an INSERT ... SELECT operation into a permanent table without a primary index from a foreign table. To disable this behavior, see Disable HASH BY RANDOM.