How does Teradata create global temporary table actually work?

Database

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 
(select
*
from
db
.a_permanent_table)
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?

Tags (1)
2 REPLIES
Junior Contributor

Re: How does Teradata create global temporary table actually work?

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?

Re: How does Teradata create global temporary table actually work?

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

create_table_function{ 

if(temporary) then create temoporary table

else create permanent table

}

function2{

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.