All Forums Database
N_Raghu 34 posts Joined 12/13
18 Oct 2014
Volatile Tables in Macros

I am writing a long macro that needed to stage data in several tables before loading into final table.
I just learnt that I cannot create more than one volatile table in a macro through error "Data definition not valid unless solitary". I also cannot mix both ddl and dml in macro. Which means I cannot use volatile tables to stage data in select into final table in the same macro. That makes volatile tables unusable to use volatile tables in a macro. Please correct me if I am wrong.
For this purpose do I need to use global temp tables? Is there any other better solutions that I can use? 

dnoeth 4628 posts Joined 11/04
18 Oct 2014

Hi Raghu,
any DDL request in Teradata must commited (no other SQL after it allowed) and a macro is always a MultiStatement Request, so there's no way to create and then use a Volatile Table in a macro.
But this is a use case for Global Temp Tables, you create the GTT before the macro and then it's a INSERT at the begin of the macro and after the EXECUTE the GTT's content is automatically deleted (if you use with ON COMMIT DELETE ROWS)


N_Raghu 34 posts Joined 12/13
18 Oct 2014

Thanks Dieter... :)


flash7gordon 29 posts Joined 05/10
05 Aug 2015

Note to Teradata product people.   What a joke.  This guy wants to use some temp tables in a macro to get to a result and he's done that a 1000 times before in ms sql and now he's being told he can't have a single temp table with dml in a teradata macro.  I'm pretty sure successive temp table work in Oracle too.  My boss used temp tables to make our company what it is, and it's a company you've heard of.   So get with the program teradata and get your parsers into the 20th century (I won't ask for the 21st) with your next release.  I won't even talk about my problem which is how to get a call to teradata to use a temp table when each invocation of Teradata is a new session.

dnoeth 4628 posts Joined 11/04
05 Aug 2015

MS SQL Server doesn't have Macros and the same for Oracle :)
If you want to create a Volatile Table and use it simply switch to a Stored Procedure instead.
Why updating very old syntax (Macro) if the new syntax is already fully implemented (SP)?
And your problem with Temp Tables will be the same in other DBMSes, a temporary table is session local, when the session ends, it's automatically dropped. Blame Standard SQL for it.
Why do you close the session instead of keeping it open?


KKT 1 post Joined 05/15
21 Jul 2016

So can I put below code in SP and get result set out when run the Proc without a cursor.



22 Jul 2016

Yes possible.

You must sign in to leave a comment.