Volatile table V/S Perminent table

Database
Enthusiast

Volatile table V/S Perminent table

I have created a volatile table with table name TEMP1
And a permanent table DATABASENAME.TEMP1

When I do a show or help or select on table TEMP1
I am able to fetch volatile table TEMP1 results as expected

But when I am doing an update or insert like

Update DATABASENAME.TABLE2
SET PT_COL1 = VT_COL1
FROM TEMP1

I am getting an ambiguous error on the TABLE , ambiguous between permanent table DATABASENAME.TEMP1 and volatile table TEMP1
This error is resolved only if I drop the DATABASENAME.TEMP1 or reframe my update statement

In a session where I have create the VT and using it in a SQL which is having update or insert with other databasename.tablenames Why the TD is first pointing to the database name given in the SQL even if I haven’t specified the DATABASENAME qualifier
Tags (1)
4 REPLIES
Senior Apprentice

Re: Volatile table V/S Perminent table

If you use a qualified tablename, the parser adds that databasename to the list of default databases for that query.

Now you got two default databases, your "default" default DB and DATABASEME and both got the same table1.

The solution for that kind of error is to qualify *all* objects, thus qualify the VT with your username.

Dieter

Re: Volatile table V/S Perminent table

hi any body is there for help me in teradata
Fan

Re: Volatile table V/S Perminent table

HI all,

i am very new to teradata, please help me with your valuable suggestions

i want to know in which case we have to choose a (volatile table,global temp table,derived table)

please help me

Enthusiast

Re: Volatile table V/S Perminent table

These all tables that you mentioned keep the data temporarily with different purpose. If you are looking to store the data permanently then you should not use either of these kind. Let us know what is your purpose and will guide what would be the best for you.