INSERT SELECT PTI and non-PTI Tables | Teradata Vantage - 17.10 - Examples: INSERT SELECT Using PTI and non-PTI Tables - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
Language
English (United States)

Examples: INSERT SELECT Statements

These statements select data from the non-PTI table normal_table and insert the rows into the PTI table ocean_buoy_1:
INSERT INTO ocean_buoy_1 SELECT * FROM normal_table;
INSERT INTO ocean_buoy_1(TD_TIMECODE, buoyid, temperature) SELECT * FROM normal_table;
INSERT INTO ocean_buoy_1(TD_TIMECODE, buoyid, temperature)
       SELECT timecode, buoyid, temperature FROM normal_table;
These statements select data from the non-PTI table normal_table2 and insert the rows into the PTI table ocean_buoy:
INSERT INTO ocean_buoy SELECT my_timecode, buoyid, temperature FROM normal_table2;
INSERT INTO ocean_buoy (TD_TIMECODE, buoyid, temperature) SELECT my_timecode, buoyid, temperature FROM normal_table2;
This is an INSERT SELECT from the PTI table ocean_buoy into the PTI table ocean_buoy_1:
INSERT INTO ocean_buoy_1 SELECT * FROM ocean_buoy;
This is an INSERT SELECT from the PTI table ocean_buoy, which has a specified time bucket duration, into the PTI table ocean_buoy_2, which does not have a specified time bucket duration:
INSERT INTO ocean_buoy_2 SELECT * FROM ocean_buoy;
This statement selects from the PTI table ocean_buoy and inserts the TD_TIMECODE, buoyid, temperature data into the PTI table ocean_buoy_1:
INSERT INTO ocean_buoy_1(TD_TIMECODE, buoyid, temperature) SELECT * FROM ocean_buoy;

Examples: Invalid INSERT SELECT Statements

You cannot specify the TD_TIMEBUCKET column in an INSERT statement. This statement returns an error:
INSERT INTO ocean_buoy_1 (TD_TIMEBUCKET, TD_TIMECODE, buoyid, temperature) SELECT * FROM ocean_buoy;
You cannot specify the TD_TIMEBUCKET column in a SELECT statement subquery. This statement returns an error:
INSERT INTO ocean_buoy_1
SELECT TD_TIMEBUCKET, TD_TIMECODE, buoyid, temperature FROM ocean_buoy;
You cannot insert values from another column into the TD_TIMEBUCKET column. In the statement below, an attempt to insert values from the my_timebucket column in the table normal_table2 into the TD_TIMEBUCKET column of the table ocean_buoy returns an error:
INSERT INTO ocean_buoy SELECT * FROM normal_table2;