CODEWITHSUNDEEP
sqlteradata
xiaodai
xiaodai

Reputation: 16004

Teradata create global temporary table

In Teradata I want to create a global temporary table and then 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-end 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 the

with data

option for temporary tables (eg. see this link). So I have to use the

no data

option. I think it also says that 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?

Upvotes: 1

Views: 10581

Answers (1)

Andrew
Andrew

Reputation: 8703

A global temporary table is really nothing but a definition of a table. When a user accesses that table, it is materialized for that user only. If another user accesses the table, another instance, completely separate from the other, is materialized. If your first statement accessing the temp table is anything other than an insert (such as your select *), it will have no rows.

Scroll down to the examples section here, I think this should help.

Upvotes: 2

Related Questions