Creating a Global Temporary Table

Go Up to Global Temporary Tables

A global temporary table is declared to a database schema via the normal CREATE TABLE statement with the following syntax:

CREATE GLOBAL TEMPORARY TABLE <table>

(<<col_def>> [, <<col_def>> | <<tconstraint>> ...])
 [ON COMMIT {PRESERVE | DELETE} ROWS];

The first argument that you supply CREATE GLOBAL TEMPORARY TABLE is the temporary table name, which is required and must be unique among all table and procedure names in the database. You must also supply at least one column definition.

The ON COMMIT clause describes whether the rows of the temporary table are deleted on each transaction commit (ON COMMIT DELETE) or are left in place (ON COMMIT PRESERVE) to be used by other transactions in the same database attachment. If the ON COMMIT is not specified then the default behavior is to DELETE ROWS on transaction commit.

There is a change in behavior in the GLOBAL TEMPORARY TABLE Support with the InterBase XE3 Update 2 release. When an SQL script is executed ISQL reported a "deadlock" if EXIT is called without COMMIT/ROLLBACK on a global temporary table. To resolve this issue, the GLOBAL TEMPORARY TABLES function has been redesigned which changes the behavior and corrects the deadlock error.

It is no longer possible for transactions emanating from the same connection to see each other's rows in a transaction-specific (ON COMMIT DELETE) temporary table. To do that, you must use a session-specific (ON COMMIT PRESERVE) temporary table that makes all rows visible to transactions starting in the same session. This is still not the same in that the rows will persist until the connection is finished.

An Global temporary table is dropped from a database schema using the normal DROP TABLE statement.