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.
INTO table clauses
{ INTO TEMP table [ WITH NO LOG ] [] | <INTO EXTERNAL clause> [] | INTO { STANDARD | [RAW] } [ owner . ] table [] [ <Storage> [] ] [ <Lock Mode> [] ] }
| 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.
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 TEMPWHERE
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 Informix® 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 Informix® ESQL/C, 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.