CREATE LOCAL TEMPORARY TABLE name (column type [NOT NULL], ... [PRIMARY KEY (column, ...)]) [ON COMMIT PRESERVE ROWS]Local temporary tables
Local temporary tables can be defined implicitly by referencing them in a INSERT statement or explicitly with a CREATE TABLE statement.
Implicitly created temp tables must have a name that starts with #.
|
Note
|
Teiid interprets local to mean that a temporary table is scoped to the session or block of the virtual procedure that creates it. This interpretation differs from the SQL specification and from the interpretation that other database vendors implement. After exiting a block or at the termination of a session, the table is dropped. Session tables and other temporary tables that a calling procedures creates are not visible to called procedures. If a temporary table of the same name is created in a called procedure, then a new instance is created. |
Creation syntax
You can create local temporary tables explicitly or implicitly.
- Explicit creation syntax
-
Local temporary tables can be defined explicitly with a CREATE TABLE statement, as in the following example:name: value
-
Use the SERIAL data type to specify a NOT NULL and auto-incrementing INTEGER column. The starting value of a SERIAL column is 1.
-
- Implicit creation syntax
-
Local temporary tables can be defined implicitly by referencing them in an INSERT statement.
INSERT INTO #name (column, ...) VALUES (value, ...) INSERT INTO #name [(column, ...)] select c1, c2 from tNoteIf #namedoes not exist, it is defined using the given column names and types from the value expressions.INSERT INTO #name (column, ...) VALUES (value, ...) INSERT INTO #name [(column, ...)] select c1, c2 from tNoteIf #namedoes not exist, it is defined using the target column names, and the types from the query-derived columns. If target columns are not supplied, the column names will match the derived column names from the query.
Drop syntax
DROP TABLE name+ In the following example, a series of statements loads a temporary table with data from 2 sources, manually inserts a record, and then uses the temporary table in a SELECT query.
Example: Local temporary tables
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;For more information about using local temporary tables, see Virtual procedures.