Copy foreign table | CREATE TABLE | Teradata Vantage - Copying Foreign Tables - 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™

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.