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

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;

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;