CREATE TEMP TABLE statement

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

Syntax

Read syntax diagramSkip visual syntax diagram
                                                   .-,-------------------------.   
                                                   V                       (1) |   
>>-CREATE TEMP TABLE -+---------------+--table--(----| Column Definition |-----+-->
                      '-IF NOT EXISTS-'                                            

>--+----------------------------------------------------------+--)-->
   |      .-,---------------------------------------------.   |      
   |      V                                         (2)   |   |      
   '-,------+-| Multiple-Column Constraint Format |-----+-+---'      
            |                       (1)                 |            
            '-| Column Definition |---------------------'            

>--+-------------+--+-------------------------------+----------->
   '-WITH NO LOG-'  |  (3)                      (4) |   
                    '-------| Storage Options |-----'   

>--+---------------------------+--+------------------------------------+-><
   |                       (5) |  |                                (6) |   
   '-| LOCK MODE Options |-----'  '-| USING Access-Method Clause |-----'   

Notes:
  1. See Column definition
  2. See Multiple-Column Constraint Format
  3. Informix® extension
  4. See Storage options for temporary tables
  5. See LOCK MODE Options
  6. See USING Access-Method Clause
Element Description Restrictions Syntax
table Name declared here for a table Must be unique in session. See Declaring a name for a temporary table Identifier

Usage

You must have the Connect privilege on the database to create a temporary table. The temporary table is visible only to the user who created it.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a temporary table of the specified name already exists in the current session.

You can also define indexes and constraints on temporary tables that you define with the CREATE TEMP TABLE statement.

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

In ESQL/C, the CREATE TEMP TABLE statement generates warnings if you use the -ansi flag or set the DBANSIWARN environment variable.


Copyright© 2019 HCL Technologies Limited