CREATE (temporary) TABLE

You can also use the CREATE TABLE statement to create a temporary table. The CREATE TABLE syntax for temporary tables is more restrictive than for permanent tables. For this reason, and because of other characteristics, temporary tables are discussed in their own section.

Syntax

CREATE TABLE <# | ##>table-name (table-element [ , table-element 
]... ) 
table-name ::= user-defined-name 
table-element ::= column-definition | table-constraint-definition 
column-definition ::= column-name data-type [ DEFAULT default-value-expression 
] [ column-constraint [ column-constraint ]... [CASE (string) | 
COLLATE collation-name ] 
column-name ::= user-defined-name 
data-type ::= data-type-name [ (precision [ , scale ] ) ] 
precision ::= integer 
scale ::= integer 
default-value-expression ::= default-value-expression + default-value-expression  
| default-value-expression - default-value-expression  
| default-value-expression * default-value-expression  
| default-value-expression / default-value-expression  
| default-value-expression & default-value-expression  
| default-value-expression | default-value-expression  
| default-value-expression ^ default-value-expression  
| ( default-value-expression ) 
| -default-value-expression  
| +default-value-expression  
| ~default-value-expression  
| ? 
| literal 
| scalar-function 
| { fn scalar-function } 
| USER 
| NULL 

		 
literal ::= 'string'  
| number  
| { d 'date-literal' }  
| { t 'time-literal' }  
| { ts 'timestamp-literal' }  

scalar-function ::= see Scalar Functions  
column-constraint ::= [ CONSTRAINT constraint-name ] col-constraint 
constraint-name ::= user-defined-name 
col-constraint ::= NOT NULL 
| UNIQUE 
| PRIMARY KEY 
| REFERENCES table-name [ ( column-name ) ] [ referential-actions ] 

table-constraint-definition ::= [ CONSTRAINT constraint-name ] table-constraint 
table-constraint ::= 	 UNIQUE ( column-name [ , column-name ]... ) 
| PRIMARY KEY ( column-name [ , column-name ]... ) 
REFERENCES table-name [ ( column-name [ , column-name ]... ) ] 
[ referential-actions ] 

referential-actions ::= referential-update-action [ referential-delete-action ] 
| referential-delete-action [ referential-update-action ] 

referential-update-action ::= ON UPDATE RESTRICT 
referential-delete-action ::= ON DELETE CASCADE 
| ON DELETE RESTRICT 

collation-name ::= 'string' | user-defined-name 

Remarks

A temporary table is used for intermediate results or working storage. Unlike in permanent tables, data in a temporary table is destroyed at some point during the SQL session or at the end of the SQL session. The data is not saved in the database.

Temporary tables are useful to narrow down intermediate results by continuing to operate on intermediate tables. Complex data operations are often easier if split into a sequence of simpler steps, which each step operating on the table result of a previous step. A temporary table is a base table. That is, the data it contains is its own. Contrast this with a view, which is an indirect representation of data in other tables.

Pervasive PSQL supports two types of temporary tables:

Both types can be used within a stored procedure or a user-defined function.

The following table summarizes characteristics of temporary tables contrasted with where the table is created or used. Characteristics can vary depending on whether the table is created or used within a stored procedure or user-defined function or outside of a stored procedure or user-defined function. Additional remarks are discussed as footnotes at the end of the table.

Compatibility With Previous Releases

Releases of Pervasive PSQL prior to Pervasive PSQL v9 Service Pack 2 permitted the naming of permanent tables starting with "#" or "##." Permanent tables starting with "#" or "##" cannot be used with Pervasive PSQL v9 Service Pack 2 or later releases. Tables starting with "#" or "##" are temporary tables and are created in the TEMPDB database.

A "table not found" error is returned if you attempt to access a permanent table starting with "#" or "##" that was created with a version of Pervasive PSQL prior to Pervasive PSQL v9 Service Pack 2.

See also Statement Separators in Pervasive PSQL User's Guide.

TEMPDB Database

The installation of Pervasive PSQL creates a system database named TEMPDB. TEMPDB holds all temporary tables. Never delete the TEMPDB database. If you remove it, you will be unable to create temporary tables.

TEMPDB is created in the install directory of the Pervasive PSQL product (by default, on Windows, C:\PVSW). If you prefer, after installation, you may change the location of the dictionary files and data files for TEMPDB. See Database Properties in Advanced Operations Guide.

---

Caution
TEMPDB is a system database for exclusive use by the database engine. Do not use TEMPDB as a repository of your permanent tables, views, stored procedures, user-defined functions, and so forth.

---

Table Names of Local Temporary Tables

The database engine automatically appends information to the names of local temporary tables to differentiate between temporary tables created across multiple sessions. The length of the appended information varies depending on the operating system.

The name of a local temporary table can be at least 10 bytes provided the number of stored procedures that create local temporary tables does not exceed 1,296. The 10 bytes include the "#" character. The 1,296 limit applies to stored procedures within the same session.

The maximum name length is 20 bytes, including the "#" character, the table name, and the appended information.

Transactions

A global temporary table can be explicitly dropped or is automatically dropped when the session in which the table was created ends. If a session other than the one that created the table uses the table in a transaction, the table is dropped when the transaction completes.

SELECT INTO

You can create a temporary table and populate it with data by using a single SELECT INTO statement. For example, SELECT * INTO #mytmptbl FROM Billing creates a local temporary table named #mytmptbl (provided #mytmptbl does not already exist). The temporary table contains the same data as the Billing table in the DEMODATA sample database.

If the SELECT INTO statement is executed a second time with the same temporary table name, an error returns because the temporary table already exists.

The SELECT INTO statement can create a temporary table from two or more tables. However, the column names must be unique in each of the tables from which the temporary table is created or an error returns.

The error can be avoided if you qualify the column names with the table names and provide an alias for each column. For example, suppose that table t1 and t2 both contain columns col1 and col2. The following statement returns an error: SELECT t1.co1, t1.col2, t2.col1, t2.col2 INTO #mytmptbl FROM t1, t2. Instead, use a statement such as this: SELECT t1.co1 c1, t1.col2 c2, t2.col1 c3, t2.col2 c4 INTO #mytmptbl FROM t1, t2.

Restrictions

Caching of Stored Procedures

Any stored procedure that references a local or a global temporary table is not cached, regardless of the cache setting(s). See SET CACHED_PROCEDURES and SET PROCEDURES_CACHE .

Examples

The following example creates a local temporary table named #b_temp and populates it with the data from the Billing table in the DEMODATA sample database.

SELECT * INTO #b_temp FROM Billing 

The following example creates a global temporary table named ##tenurefac with columns ID, Dept_Name, Building_Name, Room_Number, and a primary key based on column ID.

CREATE TABLE ##tenurefac 
  (ID                   UBIGINT, 
   Dept_Name            CHAR(20) CASE, 
Building_Name        CHAR(25) CASE, 
   Room_Number          UINTEGER, 
PRIMARY KEY (ID)) 

The following example alters temporary table ##tenurefac and adds the column Research_Grant_Amt.

ALTER TABLE ##tenurefac ADD Research_Grant_Amt DOUBLE 

The following example drops temporary table ##tenurefac.

DROP TABLE ##tenurefac 

The following example creates two temporary tables within a stored procedure, populates them with data, then assigns values to variables. The values are selected from the temporary tables.

---

Note
SELECT INTO is permitted within a stored procedure or user-defined function if used to assigned values to variables.

---

CREATE PROCEDURE "p11" () 
AS BEGIN 
DECLARE :val1_int INTEGER; 
DECLARE :val2_char VARCHAR(20); 
CREATE TABLE #t11 (col1 INT, col2 VARCHAR(20));  
CREATE TABLE #t12 (col1 INT, col2 VARCHAR(20)); 
INSERT INTO #t11 VALUES (1,'t1 col2 text'); 
INSERT INTO #t12 VALUES (2,'t2 col2 text'); 
SELECT col1 INTO :val1_int FROM #t11 WHERE col1 = 1; 
SELECT col2 INTO :val2_char FROM #t12 WHERE col1 = 2; 
PRINT :val1_int; 
PRINT :val2_char; 
COMMIT; 
END; 

CALL P11 () 

See Also

ALTER TABLE

DROP TABLE

SELECT (with into)