2.3.8 INSERT ... SELECT Statements

The SELECT query of an INSERT ... SELECT statement is offloaded to HeatWave for execution, and the result set is inserted into the specified table on the MySQL DB System. Offloading the SELECT query to HeatWave reduces INSERT ... SELECT execution time in cases where the SELECT query is long running and complex. SELECT queries that produce large result sets do not benefit from this feature due to the large number of DML operations performed on the MySQL DB system instance.

The SELECT table must be loaded in HeatWave, and the INSERT table must be present on the MySQL DB System. For example, the following statement selects data from the orders table on tHeatWave and inserts the result set into the orders2 table on the MySQL DB System:

mysql> INSERT INTO orders2 SELECT * FROM orders;

Usage notes:

• The SELECT portion of the INSERT ... SELECT statement is subject to the same HeatWave requirements and limitations as regular SELECT queries.

• Functions, operators, and attributes deprecated by MySQL Server are not supported in the SELECT query.

• The ON DUPLICATE KEY UPDATE clause is not supported.

• SELECT .. UNION ALL queries are not offloaded if the INSERT table is the same as the SELECT table because MySQL Server uses a temporary table in this case, which cannot be offloaded.

• INSERT INTO some_view SELECT statements are not offloaded. Setting use_secondary_engine=FORCED does not cause the statement to fail with an error in this case. The statement is executed on the MySQL DB System regardless of the use_secondary_engine setting.

• See: Section 2.3.1, “Query Prerequisites” and Section 2.16.7, “Other Limitations”.