Declare global temporary table

10Pints · #1 (**permalink**) 12-08-11, 08:55

This declares a table that persists for the session.
I have need of a table for just the duration of an SP call really - but no matter this would do.

Question:
Can I use this declare statement in a procedure that is called more than once per session or do I need a special procedure that is only called once at start-up in which to declare the temporary table?

Many thanks

10Pints

ARWinner · #2 (**permalink**) 12-08-11, 09:12

You can declare it in the stored procedure that is called more than once, just use the "WITH REPLACE" clause.

Andy

10Pints · #3 (**permalink**) 12-08-11, 09:47

Ah so - many thanks Andy!
Much appreciated.
10Pints

stolze · #4 (**permalink**) 12-08-11, 15:07

Another question is what you want to do with the table. Often I have seen something like this:

Code:

DECLARE GLOBAL TEMP TABLE t ...;
LOOP ...
    INSERT INTO t VALUES (...);
END LOOP;
DECLARE c CURSOR WITH RETURN FOR SELECT * FROM t;

In such a situation, you can often get along by avoid the temp table completely:

Code:

DECLARE c CURSOR WITH RETURN FOR SELECT * FROM TABLE ( VALUES (...), (...), ... ) AS t

10Pints · #5 (**permalink**) 12-20-11, 14:47

Sorry for tardy reply - been away.
Yes that would be useful in many situations: I will bank that one

Many thanks

10Pints

Internet Services

Web Development

Digital Marketing

Consumer Tech