INTO STANDARD and INTO RAW Clauses

You can use the INTO STANDARD and INTO RAW clauses to create a new permanent table that stores the result set of the SELECT statement. This syntax provides a single mechanism to specify a query, to receive the qualifying records, and to insert those query results into a permanent database table.

When using SELECT INTO to create a new permanent table, you can specify its type as STANDARD or RAW. The default type is STANDARD. You can optionally specify the storage location, extent size, and lock mode options for the new table.

The column names of the new permanent table are the names specified in the select list of the Projection clause. If an asterisk ( * ) appears as the select list of the Projection clause, the asterisk is expanded to all the column names the corresponding tables or views in the FROM clause of the SELECT statement. Any shadow columns are not expanded by the asterisk specification.

The following example creates a new raw table called ptabl to store the results of a join query:

SELECT t1col1, t1col2, t2col1 
  FROM tab1, tab2 
  WHERE t1col1 < 100 and t2col1 > 5 
    INTO RAW ptab1;

In the example above, the new ptabl table would contain the columns t1col1, t1col2 and t2col1.

All expressions other than simple column expressions must have a display label defined in the Projection clause. This display label is used as the name of the column in the new table. If a simple (or trivial) column expression has no display label, the table uses the column name. If there are duplicate display labels or column names in the select list, an error is returned.

The next example fails with error -249, because it declares no display label for the col1+5 expression:

SELECT col1+5, col2
  FROM tab1
    INTO ptab1;

The following revised query avoids the -249 error in the previous example:

SELECT col1+5 pcol1, col2
  FROM tab1
    INTO ptab1;

The corrected example above creates the standard ptabl table to store the query results in its columns pcol1 and col2.

Restrictions on result tables

As with most DDL statements, attempts to create the new result table in another database using the fully qualified table name fail with a syntax error.

It is similarly an error to create a result table with the same name as an existing table in the same database.

The SELECT INTO . . . TABLE statement cannot be used as a part of a subquery.

You can, however, use a column that is not part of the projection list of the SELECT clause as a sort key in the ORDER BY clause. The following query is valid, where the result of the join is ordered by column ptab2, which is not included in the projection list:

SELECT t1col1, t1col2, t2col1 
  FROM tab1, tab2 
  WHERE t1col1 < 100 and t2col1 > 5 
    ORDER BY t2col2 DESC 
       INTO RAW ptab2;

For a description of CREATE TABLE statement syntax that can similarly create a query result table and populate that table by inserting the qualifying rows, see AS SELECT clause.