Copying Data from a Source Table to an OTF Table - Teradata Vantage

The INSERT INTO..SELECT SQL can be used to copy data from a source table to an OTF table. The source table can be a BFS, OFS, or OTF table. The WHERE clause can be used to copy a subset of the data from the source table to the OTF table.

Syntax

INSERT INTO TABLE <datalake_name>.<OTFdatabase_name>.<OTFtable_name> 
    SELECT * | <column-name>[, ...] 
    FROM [database_name.]<source_table> |
         <datalake_name>.<OTFdatabase_name>.<OTFtable_name>
    [WHERE where_clause];
<datalake_name> ::= !! name of the data lake object
<OTFdatabase_name> ::= !! name of the database in the OTF catalog
<OTFtable_name> ::= !! name of the OTF table
<database_name> ::= !! Teradata identifier
<source_table> ::= !! Teradata table

Example

INSERT INTO datalake_glue.MyDatabase.Tab2
   SELECT * FROM datalake_glue.MyDatabase.Tab1
   WHERE country='France'; 

Usage Considerations

The following are not supported:

• Non-positional inserts.
• Writing ORC data files in Iceberg.
• Writing to a CLOB column.
• Parameterized SQL for Array Insert for an OTF table.