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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

This example uses a CREATE TABLE...AS with a SELECT subquery to copy data from a foreign table into a permanent table without a primary index.

Internally, the SELECT subquery performs an INSERT...SELECT operation with the HASH BY RANDOM clause to provide even distribution of data. The HASH BY RANDOM clause is the default for an INSERT...SELECT operation into a permanent table without a primary index from a foreign table. You can disable this behavior. See Disable HASH BY RANDOM.

Below is the foreign table definition of riverflow_0627 for this example. The JSON payload column is created by the system automatically as it detects the JSON data type.

The DefAuth_S3 authorization object was created in Example: Specifying a Definer Authorization for a Foreign Table.

CREATE FOREIGN TABLE riverflow_0627, 
EXTERNAL SECURITY DEFINER TRUSTED DefAuth_S3
USING
(
LOCATION('/S3/td-usgs.s3.amazonaws.com/JSONDATA/09380000/2018/06/27.json')
) ;

This statement defines the permanent table riverdata_0627_perm with a subquery that selects the location and payload columns from the foreign table riverflow_0627, including the data.

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