Creating Temporary Tables¶
The following table contains the syntax to create temporary tables:
CREATE TEMPORARY TABLE <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 <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:
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.
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,
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.