Wednesday, December 29, 2010

Volatile (Temp) Tables in Teradata

I keep having to look this up, so I'm going to record it here. This is the syntax for creating temp tables (volatile tables) in Teradata.

CREATE VOLATILE TABLE rgWork1 (
    
client      INT         NOT NULL,
    
flag        byteint
)
UNIQUE PRIMARY INDEX (client)
ON COMMIT PRESERVE ROWS;

DROP TABLE rgWork1;


The table is linked to the session, so you don't have to drop it if you're going to log out.

Using UNIQUE PRIMARY INDEX (...) or PRIMARY INDEX (...) is optional. It works fine without it, but leaving it off can affect performance.

However, if you leave off ON COMMIT PRESERVE ROWS, you can INSERT rows, but they won't be there when you SELECT from the table.

No comments:

Post a Comment