Global Temporary Tables (Magic xpa 2.x)

A global temporary table is visible to all sessions. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.

The association between a session and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Global temporary table names are prefixed with a double number sign (##table_name).

Global and local temporary tables are automatically created in TempDB by MSSQL, regardless of from which database the CREATE command was issued. Temporary SQL table names are created for resident tables, direct SQL tasks (result tables), and for sort operations prefixed by TEMP_, as opposed to being prefixed by T_.

Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE.

The MSSQL gateway supports storing temporary tables in the TempDB database simply by adding the number sign (‘#’ or ‘##’) as the prefix of the table name in the Data repository.