An INSERT-within-SELECT statement is a special kind of SELECT statement in which the source table is actually the result of an INSERT statement.
In the SELECT statement, the FROM clause includes an INSERT statement. If the INSERT statement includes a VALUES clause, an in-memory buffer table is created and used to store the inserted rows; otherwise a work file is created and used to store the inserted rows.
DB2® processes an INSERT within SELECT statement by first executing the INSERT statement. DB2 stores the inserted rows in either a buffer table or a work file. Then DB2 accesses the buffer table or work file and applies any predicates from the SELECT statement.
INSERT within SELECT provides an efficient method to locate any recently inserted records, especially data that DB2 inserts. In some cases, using an INSERT-within-SELECT statement is the only option for locating these records.
Graphical notation
An INSERT-within-SELECT construct consists of two subtrees. The left subtree represents the SELECT and usually consists of either a buffer table node and a BTBSCAN node or a work file node and a WFSCAN node. The right subtree represents the INSERT and usually consists of an INSERT node and a table node. The following figure shows and example that uses a BTBSCAN node in the left subtree.
