INSERT-SELECT PTI and non-PTI Tables | Teradata Vantage - 17.00 - Examples: INSERT-SELECT Using PTI and non-PTI Tables - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

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;