Copy Data from Foreign to Permanent Table | VantageCloud Lake - Example: Copying Data from Foreign Table into Permanent 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

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.