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 ( column-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 ( column-defs )
SELECT ... INTO TEMP tablename
 Yes, native SQL feature
IBM Netezza 
CREATE TEMP TABLE tablename ( column-defs )
SELECT ... INTO TEMP temptab FROM ...
 Emulated, see details
Microsoft™ SQL Server 
CREATE TABLE #tablename ( column-defs )
SELECT select-list INTO #tablename FROM ...
 Emulated, see details
Oracle® MySQL / MariadDB 
CREATE TEMPORARY TABLE tablename ( column-defs )
CREATE TEMPORARY TABLE tablename LIKE other-table
 Emulated, see details
Oracle Database Server 
CREATE GLOBAL TEMPORARY
TABLE tablename ( column-defs )
CREATE GLOBAL TEMPORARY TABLE tablename AS SELECT ...
Note: With ORACLE global temporary tables, only the data is local to the SQL session: The table is shared among all programs.
 Emulated, see details
PostgreSQL 
CREATE TEMP TABLE tablename ( column-defs )
SELECT select-list INTO TEMP tablename FROM ...
 Emulated, see details
SAP® ASE 
CREATE TABLE #tablename ( column-defs )
SELECT select-list INTO #tablename FROM ...
 Emulated, see details
SAP HANA® 
CREATE LOCAL TEMPORARY TABLE #tablename ( column-defs )
 Emulated, see details
SQLite 
CREATE TEMP TABLE tablename ( column-defs )
 Emulated, see details

Some databases even have a different behavior when using temporary tables. For example, Oracle 9i supports a kind of temporary table, but it must be created as a permanent table. The table is not specific to an SQL session: it is shared by all processes - only the data is local to a database session.

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 case, 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 IDENTITFY 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.