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;