Global Temp tables

Hi group,

I want to create several global temp tables. I've created a script:

if not object_id('tempdb..##tbl_ProductTypes') is null
begin
drop table ##tbl_ProductTypes
end
select * into ##tbl_ProductTypes from dbo.tbl_ProductTypes

This script creates a global temp table. When I run it in QA the table is created. When I close QA the table is dropped. This is correct since I found the following in BoL:
[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.]

I created a job that executes the statement above. The job exists with success, however the table is not created!

Why wasn't the table created?

TIA

Regards,

SDerix
Hi,

after finishing the job why should SQL Server keep the global temp table in life ? It is automatically dropped as the job ends.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

The job will run, create the table, and then when the job finishes the table will be dropped unless you have some other connection reading from it immediately. Or am I confused by what you are asking.

What you are trying to do is likely best served by creating a permanent table in tempdb.

if not object_id('tempdb..tbl_ProductTypes') is null
begin
drop table tempdb..tbl_ProductTypes
end
select * into tempdb..tbl_ProductTypes from dbo.tbl_ProductTypes

Now you just run this, and it will exist. If you can clean up afterwards that would be a good thing, as it will waste tempdb space. But if it is minimal it wouldn't be a big deal (just add more space to tempdb, it is more or less just a database, as long as it has plenty of space and is on optimal storage :)

|||Thnx, I was affraid of that!

I really need these 'in memory' tables. When I use 'normal' tables my performance drops from seconds to minutes! Side note: I use several temp tables...

Louis,
I'll give it a go and see what the results are.