Copy foreign table | CREATE TABLE | Teradata Vantage - 17.10 - Copying Foreign Tables - 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)
To create a copy of a foreign table with same options as an existing foreign table, use the statement CREATE TABLE AS. The form of CREATE TABLE AS determines whether you must use the WITH NO DATA clause:
  • CREATE TABLE new_foreign_table AS original_foreign_table

    You must use the WITH NO DATA clause.

  • CREATE TABLE new_foreign_table AS (SELECT select_list FROM original_foreign_table)

    You can use either the WITH NO DATA or WITH DATA clause.

CREATE TABLE AS … WITH NO DATA copies the definition of original_foreign_table to new_foreign_table, and new_foreign_table accesses the same external data as original_foreign_table.

To copy the payload data from original_foreign_table into columns of new_foreign_table:
  1. Create a view of the foreign table and use CAST to name the columns.
  2. Use the INSERT … SELECT statement to copy the payload data into the columns.