Temp Tables

Usage

Data Virtuality Server supports creating temporary, or temp, tables. Temp tables are dynamically created but are treated as any other physical table.

Temp tables can be defined implicitly by referencing them in an INSERT statement or explicitly with a CREATE TABLE statement. Implicitly created temp tables must have a name that starts with #.

Explicit Definition

SQL

CREATE LOCAL TEMPORARY TABLE x (column type [NOT NULL], ... [PRIMARY KEY (column, ...)])

Implicit Definition

Using VALUES

SQL

INSERT INTO #x (column, ...) VALUES (value, ...)

If #x does not exist, it will be defined using the given column names and types from the value expressions.

Using SELECT

SQL

INSERT INTO #x [(column, ...)] SELECT c1, c2 FROM t

If #x does not exist, it will be defined using the target column names (if not supplied, the column names will match the derived column names from the query) and the types from the query-derived columns.

Use the SERIAL data type to specify a NOT NULL and auto-incrementing INTEGER column. The starting value of a SERIAL column is 1;
To drop a temp table, use DROP TABLE:

SQL

DROP TABLE x

Primary Key Support

All key columns must be comparable;
The use of a primary key creates a clustered index that supports search improvements for comparison, in, like, and order by;
Null is an allowable primary key value, but there must be only 1 row that has an all-null key.

Limitations

With the CREATE TABLE syntax, only basic table definition (column name and type information) and an optional primary key are supported;
The ON COMMIT clause is not supported in the CREATE TABLE statement;
The Drop behaviour option is not supported in the DROP statement;
Only local temporary tables are supported. This implies that the scope of a temp table will be either to the session or the block of a virtual procedure that creates it;
Session level temp tables are not fail-over safe;
Temp tables support a READ_UNCOMMITED transaction isolation level. No locking mechanisms are available to support higher isolation levels, and a rollback result may be inconsistent across multiple transactions. If concurrent transactions are not associated with the same local temporary table or session, then the transaction isolation level is effectively SERIALIZABLE. If you want full consistency with local temporary tables, only use a connection with one transaction at a time. This mode of operation is ensured by connection pooling that tracks connections by transaction;
Lob values (XML, clob, blob) are tracked by reference rather than by value in a temporary table. Lob values from external sources inserted in a temporary table may become unreadable when the associated statement or connection is closed. The following example is a series of statements that loads a temporary table with data from two sources and with a manually inserted record and then uses that temp table in a subsequent query:
SQL
```
... 
CREATE LOCAL TEMPORARY TABLE TEMP (a integer, b integer, c integer); 
SELECT * INTO temp FROM Src1; SELECT * INTO temp FROM Src2; 
INSERT INTO temp VALUES (1,2,3); 
SELECT a,b,c FROM Src3, temp WHERE Src3.a = temp.b; 
...
```
See Virtual Procedures for more information on temp table usage.