All Forums Database
teradatatester 69 posts Joined 01/10
06 Jul 2011
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.

teradatatester 69 posts Joined 01/10
06 Jul 2011

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.

dnoeth 4628 posts Joined 11/04
07 Jul 2011

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

Dieter

gatpasrikanth1 6 posts Joined 01/12
06 Jan 2012

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

ulrich 816 posts Joined 09/09
07 Jan 2012

did you specify ON COMMIT PRESERVE ROWS?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

gatpasrikanth1 6 posts Joined 01/12
09 Jan 2012

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

Dany3R9 18 posts Joined 03/14
23 Jul 2014

thanks!

grisaitis 2 posts Joined 01/15
30 Jan 2015

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. 
 

You must sign in to leave a comment.