INTO table clauses

Use the INTO Table clauses to create a new temporary, permanent, or external table to receive the data that the SELECT statement retrieves.

This syntax fragment is part of the SELECT statement.

INTO table clauses

Notes:

¹ See INTO TEMP clause
² See INTO EXTERNAL clause
³ See INTO STANDARD and INTO RAW Clauses
⁴ See Storage options
⁵ See LOCK MODE Options


Element	Description	Restrictions	Syntax
`owner`	Authorization identifier of the owner of the result table	Without this, the user issuing the query is owner by default	Owner name
table	Name declared here of a table to receive the query results	Must be unique among names of tables, views, synonyms, and sequence objects that you own in the current database	Identifier

You must have the Connect privilege on the database to create a temporary, permanent, or external table. The name of a temporary table need not be unique among the identifiers of temporary tables in other user sessions.

Column names in the permanent, temporary, or external table must be specified in the Projection clause, where you must supply a display label for all expressions that are not simple column expressions. The display label becomes the column name in the permanent, temporary, or external table. If you do not declare a display label for a simple column expression, the resulting new table uses the column name from the select list of the Projection clause.

The following INTO TEMP example creates the pushdate table with two columns, customer_num and slowdate:

SELECT customer_num, call_dtime + 5 UNITS DAY slowdate
   FROM cust_calls INTO TEMP pushdate;

The following INTO STANDARD example creates the stab1 table with two columns, fcol1 and col2:

SELECT col1::FLOAT fcol1, col2
   FROM tab1 INTO STANDARD stab1;

Here col1 is an INTEGER column in the tab1 table from which the query retrieves data, but the fcol1 values are cast to FLOAT in the resulting stab1 table. A query that omits the STANDARD keyword would create the same result table, because STANDARD is the default table type.

Results when no rows are returned

When you use an INTO Table clause combined with the WHERE clause, and no rows are returned, the SQLNOTFOUND value is 100 in ANSI-compliant databases and 0 in databases that are not ANSI compliant. If the SELECT INTO TEMP...;WHERE...; statement is a part of a multistatement PREPARE and no rows are returned, the SQLNOTFOUND value is 100 for both ANSI-compliant databases and databases that are not ANSI-compliant.

This release of HCL OneDB™ continues to process the remaining statements of a multistatement prepared object after encountering the SQLNOTFOUND value of 100. You can maintain the legacy behavior, however, of not executing the remaining prepared statements by setting the IFX_MULTIPREPSTMT environment variable to 1.

Restrictions with INTO table clauses in ESQL/C

In , do not use both the INTO table clause and the INTO variable clause in the same query. If you do, no results are returned to the program variables and the SQLCODE variable is set to a negative value. For more information about the INTO variable clause, see INTO Clause.