Temporary tables
Informix®
CREATE TEMP TABLE DDL instruction or with SELECT ... INTO TEMP
statement:CREATE TEMP TABLE tt1 ( pkey INT, name VARCHAR(50) )
CREATE TEMP TABLE tt2 ( pkey INT, name VARCHAR(50) ) WITH NO LOG
SELECT * FROM tab1 WHERE pkey > 100 INTO TEMP tt2Temporary tables are automatically dropped when the SQL session ends, but they can also be
dropped with the DROP TABLE command. There is no name conflict when several users
create temporary tables with the same name.
BDL REPORTs can create a temporary table when the rows are not sorted externally (by the source SQL statement).
Informix allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.
When creating temporary tables in Informix, the
WITH NO LOG clause can be used to avoid the overhead of recording DML operations in
transaction logs.
Microsoft™ SQL Server
CREATE TABLE #temp1 ( kcol INTEGER, .... )The INTO clause can be used within a SELECT statement to create
and fill a temporary table in one step:
SELECT * INTO #temp2 FROM customers WHERE ...Solution
In BDL, Informix temporary tables instructions are converted to generate native SQL Server temporary tables.
dbi.database.dsname.ifxemul.temptables = { true | false }