Temp Tables
Usage
CData 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
CREATE LOCAL TEMPORARY TABLE x (column type [NOT NULL], ... [PRIMARY KEY (column, ...)])
Implicit Definition
Using VALUES
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
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 aNOT NULL
and auto-incrementingINTEGER
column. The starting value of aSERIAL
column is 1;To drop a temp table, use
DROP TABLE
:
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 theDROP
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 effectivelySERIALIZABLE
. 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 Also
Virtual Procedures for more on temp table usage