VOLATILE TABLE - Where is it located?

Database
Junior Supporter

VOLATILE TABLE - Where is it located?

When you create a VOLATILE TABLE what location is it created in? I know it is a temp table that is gone when the session ends.

The following SQL will give me the SQL that was used to create the table:
Show table myVolatileTableName;

I would like to be able to look at the structure of VOLATILE TABLE in SQL Assistant or Teradata Administrator.
Tags (2)
7 REPLIES
Junior Supporter

Re: VOLATILE TABLE - Where is it located?

In SQL Data Definition Language – Syntax and Examples page 778 it shows:
HELP VOLATILE TABLE sales_temp;

The above didn’t work for me, but this SQL does:
HELP TABLE myTableName;

The result gives me the structure info, but I still would like to know where a VOLATILE TABLE is located when it is created so I can see it directly in SQL Assistant or Teradata Administrator.
Senior Apprentice

Re: VOLATILE TABLE - Where is it located?

Volatile tables are stored in SPOOL and there's no info stored in any system table.

Thus the only way to retrieve the metadata of a VT is a HELP/SHOW TABLE.

If you forgot the name you can get all the existing VTs in your current session using
HELP VOLATILE TABLE;

Dieter
Enthusiast

Re: VOLATILE TABLE - Where is it located?

can i see contents of volatile table

create volatile table xyz.test;.....set of statements.

if say sel * from xyz.test...i am not getting any result.
Thanks
Sree
Supporter

Re: VOLATILE TABLE - Where is it located?

did you specify ON COMMIT PRESERVE ROWS?

Enthusiast

Re: VOLATILE TABLE - Where is it located?

oh i missed that.thanks now i am able to....
Teradata Employee

Re: VOLATILE TABLE - Where is it located?

thanks!

Re: VOLATILE TABLE - Where is it located?

If you forgot the name you can get all the existing VTs in your current session using

HELP VOLATILE TABLE;

Thanks, dieter! Just what I was looking for. 

Initially I tried 

help database <user ID>;

which didn't go very well.