Hi experts.

I have code where i create a table in database (Netezza) like this:

TABLE FILE TABLE_A
PRINT *
ON TABLE HOLD AS TMP FORMAT SQLNEZ

This creates every time i run the code a persistent table, which leads to concurrency problems.

What i would like to is create a table that is like a temporary table which lives in the session and has a unique name being discarted when session ends.

Is there any parameter to do that?

Thanks in advance.

This message has been edited. Last edited by: <Kathryn Henning>,

There's no parameter to do that because the temporary table is in the database. You would need to use the DROP TABLE command somewhere in your job flow. However, with Oracle, DROP TABLE is a privileged command, I would assume that it is in Netezza as well.

Before you execute code to generate new table drop existing table

ENGINE SQLNEZ SET DEFAULT_CONNECTION VirtualDebit
SQL SQLNEZ DROP TABLE TMP ;
END

FYI, for MS SQL Server only, you can create a temporary table by prefixing the table name with a #:

TABLE FILE MSCRM_LIST
PRINT
LISTNAME
WHERE RECORDLIMIT EQ 10
ON TABLE HOLD AS #HLIST FORMAT SQLMSS
END

Just yesterday I stumbled about something like that in the documentation of the FORMAT keyword.

If your data is from the same database, apparently you can use FORMAT SAMEDB.
That will create a temporary table in the same DB with your table results.