How to Create Volatile Table in Teradata?

Volatile tables are as same as simple tables but with a small difference i.e. they are volatile in nature. 

Like a simple table, the volatile table is also formed by the user and can only be used until the user is logged into. Once the user is logged off or disconnects, the Teradata manager automatically drops the table from the session.

After dropping the table by Teradata manager, the data and definition inserted in the volatile table will be erased automatically.

How can you use the volatile table?

Suppose, you are the user and working with the Teradata database and you have to form a couple of tables in the same database. The first option is you have to create simple tables in the same database and drop them after use.

The second option is you can create volatile tables whose data and definition are automatically dropped by the Teradata database after you logged off from the database and that will be the more smart way. 

We will follow the below syntax for volatile table syntax.

Syntax:

CREATE [SET | MULTISET] VOLATILE TABLE TABEL_NAME 
(
COLUMN1 DATATYPE;
COLUMN2 DATATYPE;
.
.
.
COLUMN_N datatype)
<INDEX_DEFINITION>
ON COMMIT [DELETE|PRESERVE] ROWS;

Example:

The following example will create a volatile table of the name ‘geek’.

CREATE VOLATILE TABLE GEEK
(
ROLLNO INT,
FIRST_NAME VARCHAR(15),
LAST_NAME VARCHAR(15)
)
PRIMARY INDEX (ROLLNO)
ON COMMIT PRESERVE ROWS;

Here, you can clearly see the last line written as ON COMMIT PRESERVE ROWS this line will preserve the data after inserting it by you.

The default value is ON COMMIT DELETE ROWS.

Data insertion in the volatile table:

Let’s insert some data in the volatile table.

INSERT INTO GEEK VALUES (1,'Aman','Goyal');
INSERT INTO GEEK VALUES (2,'Pritam','Soni');
INSERT INTO GEEK VALUES (3,'Swati','Jain');

Select data from the volatile table:

We will run the select statement into the volatile table.

SELECT * FROM GEEK ORDER BY ROLLNO;

Output:

At last, if we disconnect from the current session and after re-logging, run the same select statement again, we will find that the table student does not exist anymore in the database.

Output after re-logging:

SELECT * FROM GEEK ORDER BY ROLLNO;
*** Failure 3807 Object 'GEEK' does not exist.
Statement# 1, Info =0
*** Total elapsed time was 1 second