Temporary tables

Syntax for temporary table creation is not unique across all database engines.

Not all database servers support temporary tables. The engines supporting this feature often provide it with a specific table creation statement:

Table 1. Database server support of temporary tables
Database Server Type	Native temp table creation syntax	Temp table support
IBM® DB2® LUW	`DECLARE GLOBAL TEMPORARY TABLE tablename ( col-defs ) DECLARE GLOBAL TEMPORARY TABLE tablename AS (SELECT ...)` Note: DB2 Version 11.1 supports CREATE TEMP TABLE syntax for compatibility with Netezza.	Emulated, see details
IBM Informix®	`CREATE TEMP TABLE tablename ( col-defs ) SELECT ... INTO TEMP tablename`	Yes, native SQL feature
IBM Netezza	`CREATE TEMP TABLE tablename ( col-defs ) SELECT ... INTO TEMP temptab FROM ...`	Emulated, see details
Microsoft™ SQL Server	`CREATE TABLE #tablename ( col-defs ) SELECT select-list INTO #tablename FROM ...`	Emulated, see details
Oracle® MySQL / MariadDB	`CREATE TEMPORARY TABLE tablename ( col-defs ) CREATE TEMPORARY TABLE tablename LIKE other-table`	Emulated, see details
Oracle Database Server	`CREATE GLOBAL TEMPORARY TABLE tablename ( col-defs ) CREATE GLOBAL TEMPORARY TABLE tablename AS SELECT ...` or, since Oracle 18c: `CREATE PRIVATE TEMPORARY TABLE tablename ( col-defs ) CREATE PRIVATE TEMPORARY TABLE tablename AS SELECT ...`	Emulated, see details
PostgreSQL	`CREATE TEMP TABLE tablename ( col-defs ) SELECT select-list INTO TEMP tablename FROM ...`	Emulated, see details
SAP® ASE	`CREATE TABLE #tablename ( col-defs ) SELECT select-list INTO #tablename FROM ...`	Emulated, see details
SAP HANA®	`CREATE LOCAL TEMPORARY TABLE #tablename ( col-defs )`	Emulated, see details
SQLite	`CREATE TEMP TABLE tablename ( col-defs )`	Emulated, see details

The behavior and limitations of temporary tables varies with the type of database server. See database adaptation guides for more details.

Important:

Simple Informix-style SQL statement creating temporary tables can be converted to a native SQL equivalent instruction. However, complex SQL statements such as SELECT .. INTO TEMP with subqueries may fail. In such cases, create a view from the complex query and then create the temp table from the view. Or, disable Informix emulation and use the native SQL syntax to create the temporary table (EXECUTE IMMEDIATE "/* fglhint_no_ifxemul */ …")

With Informix SQL, if the source table has a column defined as SERIAL or BIGSERIAL, a SELECT ... INTO TEMP will produce a new temp table with an auto-incremented serial column. With the SELECT … INTO TEMP emulation for non-Informix databases, not using the native sequence generators (such as IDENTITY columns in SQL Server), the resulting temporary table will get a simple INTEGER or BIGINT column, instead of an auto-incremented column.

Consider reviewing programs using temporary tables, and adapt the code to create temporary tables with native SQL syntax.