TO TABLE

When a statement is issued that makes or attempts to make a modification to one or more rows of a table (such as INSERT, DELETE, UPDATE, or SELECT INTO), the TO TABLE statement shows the target table being modified. For operations that require an intermediate step that inserts rows into a worktable (discussed later), TO TABLE indicates that the results are going to the worktable, rather than to a user table.

The following examples illustrate the use of the TO TABLE statement:

Query 1:

INSERT publishers
VALUES ('9904', 'NewPubs', 'Seattle', 'WA', 'USA')

SHOWPLAN 1:

STEP 1
The type of query is INSERT
The update mode is direct
TO TABLE
publishers

Query 2:

UPDATE publishers
SET city = 'Los Angeles'
WHERE pub_id = '1389'

SHOWPLAN 2:

STEP 1
The type of query is UPDATE
The update mode is direct
FROM TABLE
publishers
Nested iteration
Table Scan
TO TABLE
publishers

Notice that the SHOWPLAN for the second query indicates that the publishers table is used both as the FROM TABLE as well as the TO TABLE. In the case of UPDATE operations, the optimizer reads the table that contains the rows to be updated, resulting in the FROM TABLE statement, and then modifies the rows, resulting in the TO TABLE statement.