0

Currently we have a query that forecast income for direct debit products. This Query uses permanent tables opposed to temp tables as the query use Dynamic SQL and temp tables could not be seen outside the scope of that dynamic SQL execution.

What I want to do is put them into a stored procedure using global temp tables to get round the out of scope issue with temp tables. My question is if they are never run in parallel and only sequentially will these global temp tables be deleted once the stored procedure has run like normal temp tables? Or because they are global will they be left in the tempdb?

Also please note I have looked into insert into table method but sadly as per my comment below we use around 1000 tables to create the final output.

Jackt153
  • 1
  • 1
  • There are alternatives to global temporary tables, such as using `insert . . . exec`. You might want to consider other solutions. – Gordon Linoff Apr 20 '16 at 18:46
  • Hi I am unable to do so it would be to complex. The query itself generates a table for every month of the year for 12 years worth of previous income. Then we have 7 different product lines so that 7 *12 *12 so a lot of small tables. Sadly this is something I have inherited and not created myself. – Jackt153 Apr 20 '16 at 18:51
  • I read somewhere (don't remember): If you create a Global temp table, the table will be available for other sessions ONLY UNTIL your connection is alive. if you disconnect and NO ONE is in the middle of reading data from that table, the table will drop. This means if you disconnect, whoever is reading form table, will successfully finish his operation and table will drop right after all other sessions are not doing anything with table. I have tested this and it is true. – FLICKER Apr 20 '16 at 19:59

1 Answers1

0

This elaborates on Flicker's comment. The documentation is:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Hence, these seem to do what you want.

I am not thrilled with the use of global temporary tables for this purpose. But they do seem to meet your needs. Well, they are not deleted when the stored procedure finishes, just when the session ends. You could force this by using dynamic SQL when you define the tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786