Copy foreign table | CREATE TABLE | Teradata Vantage - 17.05 - 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.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

You can use CREATE TABLE ... AS WITH NO DATA to create a copy of a foreign table with same options as source foreign table. When you specify the WITH NO DATA clause, the definition of the source foreign table is copied to the target foreign table and the target foreign table accesses the same external data as the source foreign table. See Example: Copying a Foreign Table, Not Including Data.

The following table options are not supported for foreign tables:
  • BLOCKCOMPRESSIONLEVEL
  • BLOCKCOMPRESSIONALGORITHM
  • BLOCKCOMPRESSION
  • MERGEBLOCKRATIO
  • CHECKSUM
  • DATABLOCKSIZE
  • FREESPACE
  • Journal table

You cannot use CREATE TABLE ... AS WITH DATA with a foreign table as the source in a SELECT subquery.

You can create a view of the foreign table and use CAST to name the columns. Then, use an INSERT...SELECT operation to copy the payload data into columns of the permanent table. See Example: Importing External Data From a Foreign Table into a Permanent Table.