Create Volatile Table / Teradata

Volatile Tables are temprorary tables in teradata.

Common syntax;

CREATE MULTISET VOLATILE TABLE vt_tablename AS (SEL columnName1,columnName2,columnName3 FROM my_real_table) WITH DATA ON COMMIT PRESERVE ROWS;

The WITH DATA ON COMMIT PRESERVE ROWS statement keeps the data in the volatile table…otherwise you will create the structure but there will be no records.

You can make the volatile tables as SET or MULTISET.


The volatile table will persist as long as your session is connected. When session ended, volatile table will drop.

You can and should collect stats on volatile tables.

In addtion you can add primary index to volatile table with following syntax ;

CREATE MULTISET VOLATILE TABLE vt_tablename AS (SEL columnName1,columnName2,columnName3 FROM my_real_table) WITH DATA ON COMMIT PRESERVE ROWS ; PRIMARY INDEX( columnName1) ;