All Forums Database
xiaodai 2 posts Joined 12/14
23 Feb 2015
How does Teradata create global temporary table actually work?

n Teradata I want to create a global temporary table and the reference this global table in other queries. I don't want to create a volatile table as I am using Teradata through another front end and that front has to be able to tell whether the temporary table exists, hence volatile tables are not an option as it does not exist in the data dictionary.
Here's my code to create the temporary table hihihi.

create set global temporary table hihihi as
with no data
on commit preserve rows;

select * from hihihi;
According to the research I have done I can't use with data for temporary tables (eg. see this link). So I have to use the no data, and it also says when I reference this temporary table the data will "materialize".
However when I do the select as below

select * from hihihi;
nothing is returned? What am I missing in my understanding of global temporary tables?

dnoeth 4628 posts Joined 11/04
24 Feb 2015

A Global Temporary Table is a template to be used by multiple sessions/users.
It's created once and then each session can materialize it's own version using Insert.
Why do you think you must use a GTT instead of a Volatile table?
Can't you create a random table name within the application?


xiaodai 2 posts Joined 12/14
24 Feb 2015

Thanks. I don't get this clear explanation from reading the documentation.
The application has backends for alot of databases and Teradata is the one I am trying to build for. There is a bit of generic code in the application that goes something like this
if(temporary) then create temoporary table
else create permanent table
if(table_exists(sometable) then do this
Since the create_table_function and function2 are indepedent and they are meant to work for all databases, if I create a temporary table using volatile then function2 won't work as it can't find the table in Teradata. In other databases e.g. sqlite I can create a "temporary" table  and function can still check if it exists.

You must sign in to leave a comment.