16.20 - Examples: INSERT-SELECT Using PTI and non-PTI Tables - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)
Last Update
2019-05-02

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;