Temporary Tables¶

A Temporary table is a special type of view that only lives for the duration of the session of the user that creates it, or until she deletes it. The view and the data inserted in it are only visible by the user that creates the table.

The data of these tables is stored in the database where the data is cached, instead of in an external data source. Unlike the other types of views, its schema and its data are completely managed from Virtual DataPort.

When you create a temporary table, the Server creates a table (a real table, not a temporary one) in the cache database that will hold the data you insert in it.

You can insert rows into temporary tables but cannot delete or update them.

Note

Before creating any temporary table, enable the cache engine. To do this, follow the steps described in the section Configuring the Cache of the Administration Guide.

The differences between temporary tables and materialized tables are the following:

Differences between temporary tables and materialized tables¶
Temporary Tables	Materialized Tables
Only visible from the session from which the temporary table is created.	Visible to all the users with the appropriate privileges.
Automatically deleted once the user closes the session to the database. I.e. when creating the table from the “VQL Shell” of the Administration Tool, until the user disconnects or switches to another database.	They are persisted across session.
Two users may create a temporary table each, both with the same name. Each user will only have access to her own.	There cannot be two materialized tables with the same name.
They cannot be created nor managed graphically from the Administration Tool. They can be created only from the “VQL Shell”.	They cannot be graphically created from the Administration Tool (only from the “VQL Shell”), but after refreshing the Administration Tool (clicking “Refresh” on the “File” menu), they can be managed graphically.
You cannot create derived views over temporary tables.	You can create derived views over materialized tables.

After 48 hours of creating a temporary table, Virtual DataPort removes the table from the database. It does so to prevent that the data of a table is kept in the database indefinitely. This scenario may occur if there is an error while a user closes her session and she has created a table.

Creating Temporary Tables¶

This is the syntax of the command CREATE TEMPORARY TABLE:

Syntax of the statement CREATE TEMPORARY TABLE¶

CREATE TEMPORARY TABLE [<database:identifier>.]<name:identifier>
  (
      <VDP type field> [, <VDP type field> ]*
    | <SQL type field> [, <SQL type field> ]*
  )
  [ FOLDER = <literal> ]
  [ <primary key> ]

<VDP type field> ::=
    <name:identifier> : <Virtual DataPort data type>
        [ ( <property list> ) ]

<Virtual DataPort data type> ::= (see Figure 1)

<SQL type field> ::= <name:identifier> <SQL type>
        [ ( <property> [, <property> ]+ ) ]

<SQL type> ::=
    | BIT ( <integer> )
    | BIT VARYING ( <integer> )
    | BLOB
    | BOOLEAN
    | CHAR ( <integer> )
    | CHARACTER ( <integer> )
    | CHARACTER VARYING ( <integer> )
    | DATE
    | DECIMAL
    | DECIMAL ( <integer>, <integer> )
    | DOUBLE
    | DOUBLE PRECISION
    | FLOAT
    | INT
    | INTEGER
    | LONG
    | NCHAR ( <integer> )
    | NUMERIC
    | NUMERIC ( <integer> , <integer> )
    | NVARCHAR ( <integer> )
    | REAL
    | SMALLINT
    | TEXT
    | TIMESTAMP
    | TIMESTAMP WITH TIME ZONE
    | VARCHAR ( <integer> )

CREATE TEMPORARY TABLE [<database:identifier>.]<name:identifier>
    [ ( <field without type> [, <field without type> ]* ) ]
    AS <select>

<field without type> ::= <name:identifier> [ ( <property list> ) ]

<property list> ::=
    <property name:identifier> [ = <value:identifier> ]
      [, <property name i:identifier> [ = <value i:identifier> ] ]*

<primary key> ::=
  [ CONSTRAINT <name:literal> ]
  PRIMARY KEY ( <field name:literal> [, <field name:literal> ]* )

<select> ::= (see Syntax of the SELECT statement)

To indicate the type of the fields you can either use the native VQL data types or the SQL data types. The SQL data types are transformed into

For example:

Example of CREATE TEMPORARY TABLE¶

CREATE TEMPORARY TABLE employee (
      ssn : text
    , first_name : text
    , surname : text
)
CONSTRAINT 'primary_key_employee' PRIMARY KEY ('ssn');

This creates a temporary table with the field “ssn” as primary key. Note that Virtual DataPort does not enforce the primary keys of views. You have to make sure you do not insert repeated values for this field.

Example of creating a temporary table from the result of a query¶

CREATE TEMPORARY TABLE employee_dept_10 AS SELECT * FROM employee
WHERE dept_no = 10;

After creating a temporary table, you can insert data into it as with a regular view and also insert several rows in the same statement.

For example,

Examples of how to inserting data in a temporary table¶

INSERT INTO employee (first_name, surname, ssn)
VALUES ('Emma', 'Smith', '987-65-4321');

INSERT INTO employee (SELECT 'Robert', 'Brown', '123-45-6789' from
Dual());

INSERT INTO employee (first_name, surname, ssn) VALUES
('Emma', 'Smith', '987-65-4321')
, ('Elizabeth', 'Brown', '987-65-4322');

See more details about the INSERT clause in the section INSERT Statement.

Privileges Required¶

The user creating the temporary table needs to have at least one of these privileges:

The privilege CREATE over the database.
The privilege CREATE_VIEW over the database.
The role create_temporary_table. Users with this role can create temporary tables on any database.

USER MANUALS

Temporary Tables¶

Creating Temporary Tables¶

Privileges Required¶