Informix Guide to SQL: Syntax SQL Statements

Use the CREATE Temporary TABLE statement to create a temporary table in the current database.

Usage

If you have the Connect privilege on a database, you can create a temporary table. However, you are the only user who can see the temporary table.

In DB-Access, using the CREATE Temporary Table statement outside the CREATE SCHEMA statement generates warnings if you set DBANSIWARN.

The CREATE TABLE statement generates warnings if you use the -ansi flag or set the DBANSIWARN environment variable.

Once a TEMP table is created, you can build indexes on the table.

If your database does not have logging, the table behaves in the same way as a table that uses the WITH NO LOG option.

Use the INTO Scratch clause to reduce the overhead of transaction logging. A scratch table is a nonlogging temporary table that does not support indexes or referential constraints. A scratch table is identical to a TEMP table created with the WITH NO LOG option. Operations on scratch tables are not included in transaction-log operations.

A temporary table is associated with a session, not a database. Therefore, when you create a temporary table, you cannot create another temporary table with the same name (even for another database) until you drop the first temporary table or end the session.

The name must be different from existing table, view, or synonym names in the current database; however, it need not be different from other temporary table names used by other users.

In an ANSI-compliant database, the combination owner.table must be unique in the database.

Informix recommends that you use a scratch table rather than a TEMPWITH NO LOG table. The behavior of a temporary table that you create with the WITH NO LOG option is the same as that of a scratch table.

Use the WITH NO LOG option to reduce the overhead of transaction logging. If you use the WITH NO LOG option, operations on the temporary table are not included in the transaction-log operations.

You must use the WITH NO LOG option on temporary tables you create in temporary dbspaces.

If you use the WITH NO LOG option in a database that does not use logging, the WITH NO LOG option is ignored.

Once you turn off logging on a temporary table, you cannot turn it back on; a temporary table is, therefore, always logged or never logged.

The following example shows how to prevent logging temporary tables in a database that uses logging:

CREATE  TEMP  TABLE  tab2  

	(fname  CHAR(15),  

	 lname  CHAR(15))

	WITH  NO  LOG 

Use the column definition portion of CREATE Temporary TABLE to list the name, data type, default value, and constraints of a single column.

This portion of the CREATE Temporary TABLE statement is almost identical to the corresponding section in the CREATE TABLE statement. The difference is that fewer types of constraints are allowed in a temporary table.

Use the single column constraint format to create one or more data-integrity constraints for a single column in a temporary table.

The following table indicates where you can find detailed discussions of specific constraints.

Constraints you define on temporary tables are always enabled.

Use the multiple-column constraint format to associate one or more columns with a constraint. This alternative to the single-column constraint format allows you to associate multiple columns with a constraint.

This alternative to the column-level constraints portion of the CREATE TABLE statement allows you to associate multiple columns with a constraint.

Constraints you define on temporary tables are always enabled.

The following table indicates where you can find detailed discussions of specific constraints.

The CREATE TABLE options let you specify storage locations, locking modes, and user-defined access methods

You cannot specify initial and next extents for a temporary table. Extents for a temporary table are always eight pages.

Use the storage-option portion of the CREATE Temporary Table statement to specify the distribution scheme for the table.

If you are using Enterprise Decision Server, you can fragment a temporary table across multiple dbspaces that different coservers manage.

If you plan to create a fragmented, unique index on a temporary table, you must specify an explicit expression-based distribution scheme for a temporary table in the CREATE Temporary TABLE statement.

The distribution scheme that you specify with the CREATE Temporary TABLE statement (either with the IN clause or the FRAGMENT BY clause) takes precedence over the information specified in the DBSPACETEMP environment variable and the DBSPSCETEMP configuration parameter.

For temporary tables for which you do not specify an explicit distribution scheme, each temporary table that you create round-robins to a dbspace specified by the DBSPACETEMP environment variable or the DBSPACETEMP configuration parameter if the environment variable is not set. For example, if you create three temporary tables, the first one goes into the dbspace called tempspc1, the second one goes into tempspc2, and the third one goes into tempspc3.

This behavior also applies temporary tables that you create with SELECT...INTO TEMP or SELECT...INTO SCRATCH.

For more information on the DBSPACETEMP environment variable, see Informix Guide to SQL: Reference.

For more information on the DBSPACETEMP configuration parameter, see your Administrator's Reference.

The following example shows how to insert data into a temporary table called result_tmp to output to a file the results of a user-defined function (f_one) that returns multiple rows.

CREATE TEMP TABLE result_tmp( ... );

INSERT INTO result_tmp EXECUTE FUNCTION f_one();

UNLOAD TO 'file' SELECT * FROM temp1;

In Enterprise Decision Server, to recreate this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

Temporary tables differ from permanent tables in a number of ways. Temporary tables:

• have fewer types of constraints available.
• have fewer options that you can specify.
• are not preserved.

For more information, see Duration of Temporary Tables.

• are not visible to other users or sessions.
• do not appear in the system catalogs.

You can use the following data definition statements on a temporary table from a secondary coserver: CREATE Temporary TABLE, CREATE INDEX, CREATE SCHEMA, DROP TABLE, and DROP INDEX.

You cannot use the INFO statement and the Info Menu option with temporary tables.

The duration of a temporary table depends on whether or not that table is logged.

A logged, temporary table exists until one of the following situations occurs:

• The application disconnects.
• A DROP TABLE statement is issued on the temporary table.
• The database is closed.

When any of these events occur, the temporary table is deleted.

Nonlogging temporary tables include temp tables created with the WITH NO LOG option and SCRATCH tables.

A nonlogging, temporary table exists until one of the following situations occurs:

• The application disconnects.
• A DROP TABLE statement is issued on the temporary table.

Because these tables do not disappear when the database is closed, you can use a nonlogging temporary table to transfer data from one database to another while the application remains connected.

Related statements: ALTER TABLE, CREATE TABLE, CREATE DATABASE, DROP TABLE, and SELECT

For additional information about the DBANSIWARN and DBSPACETEMP environment variables, refer to the Informix Guide to SQL: Reference.

For additional information about the ONCONFIG parameter DBSPACETEMP, see your Administrator's Guide.