|
|
Posted by Erland Sommarskog on 09/08/06 21:43
Ed Murphy (emurphy42@socal.rr.com) writes:
> Original Poster, if you're still reading this thread: it would almost
> certainly be better to use a table with a static name, moving the
> dynamic portion to a column within that table, e.g.
>
> -- ##TICKETS has structure (TICKET VARCHAR(20), DATA XML)
>
> INSERT INTO ##TICKETS (TICKET, DATA) VALUES (@TICKET, @DATA)
>
> I'm not familiar with global temp tables (I've learned a little by
> Googling today) so I'll let someone else address their pros/cons.
Global temp tables is a fairly hopeless feature. Since the table is
visible to all processes, you need to make the name unique in some
other way, and this leads to a mess with dynamic SQL.
When I worked on my article for sharing data between stored procedures,
http://www.sommarskog.se/share_data.html, the idea occurred to me that
global temp tables may be could be used for this. But this a global temp
table goes away when the processes that created it goes away, it was
difficult to make anything useful of it. (If another process has an
active reference to the temp table, when the creating process exits,
the global temp table hangs around until the other process has completed
its query.)
Neverheless, I tipped a colleague to use a global temp table recently.
He was working with an export job, and ran into some problem with
the queryout option in BCP. Instead he dumped his data into a global
temp table, which he then could access from BCP. But his situation
was such that he could assume that there not two instances of the
job at the same time, so he did not need to have a dynamic name.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|