| SQL programming / SQL portability | |
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:
| Database Server Type | Temp table creation syntax | Local to SQL session? |
|---|---|---|
| Genero db | CREATE TEMP TABLE tablename ( column-defs ) SELECT ... INTO TEMP tablename |
Yes |
| IBM® DB2® UDB | DECLARE GLOBAL TEMPORARY TABLE tablename ( column-defs ) DECLARE GLOBAL TEMPORARY TABLE tablename AS ( SELECT ... ) |
Yes |
| IBM Informix® | CREATE TEMP TABLE tablename ( column-defs ) SELECT ... INTO TEMP tablename |
Yes |
| Microsoft™ SQL Server | CREATE TABLE #tablename ( column-defs ) SELECT select-list INTO #tablename FROM ... |
Yes |
| MySQL | CREATE TEMPORARY TABLE tablename ( column-defs ) CREATE TEMPORARY TABLE tablename LIKE other-table |
Yes |
| Oracle Database Server | CREATE GLOBAL TEMPORARY TABLE tablename ( column-defs ) CREATE GLOBAL TEMPORARY TABLE tablename AS SELECT ... |
No: only data is local to session |
| PostgreSQL | CREATE TEMP TABLE tablename ( column-defs ) SELECT select-list INTO TEMP tablename FROM ... |
Yes |
| Sybase ASE | CREATE TABLE #tablename ( column-defs ) SELECT select-list INTO #tablename FROM ... |
Yes |
| SQLite |
CREATE TEMP TABLE tablename ( column-defs ) |
Yes |
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.
You must review the programs using temporary tables, and adapt the code to use database-specific temporary tables.