How do you create ##Temp tables if they don't exist, use them if they do?

[M Bourgon]

I have two SPs, call them Daily and Weekly. Weekly will always call
Daily, but Daily can run on its own. I currently use a global temp
table because certain things I do with it won't work with a local temp
table or table variable.

I have been trying to get code so that if the table already exists, it
just keeps going and uses it, but creates the temp table if it doesn't
exist. Unfortunately, no matter how I try to do it, it always attempts
to create it, raising an error and breaking the code.
create table ##load_file_log (id int identity(1,1),contents
varchar(1000))
insert into ##load_file_log (contents) values ('test record')

IF object_id('tempdb..##load_file_log') IS not NULL
print 'exists'
ELSE
create table ##load_file_log (id int identity(1,1),contents
varchar(1000))

select * from ##load_file_log
drop table ##load_file_log

If I change it to IS NULL, the same error occurs (Server: Msg 2714,
Level 16, State 1, Line 7
There is already an object named '##load_file_log' in the database.)

I have found one way to do it, but it seems a bit...clunky.

IF object_id('tempdb..##load_file_log') IS NULL
exec ('create table ##load_file_log (id int identity(1,1),contents
varchar(1000))')

I'll use that for now, but is there something I missed?
Thanks.

[David Portas]

M Bourgon wrote:

I have two SPs, call them Daily and Weekly. Weekly will always call
Daily, but Daily can run on its own. I currently use a global temp
table because certain things I do with it won't work with a local temp
table or table variable.

I have been trying to get code so that if the table already exists, it
just keeps going and uses it, but creates the temp table if it doesn't
exist. Unfortunately, no matter how I try to do it, it always attempts
to create it, raising an error and breaking the code.
create table ##load_file_log (id int identity(1,1),contents
varchar(1000))
insert into ##load_file_log (contents) values ('test record')

IF object_id('tempdb..##load_file_log') IS not NULL
print 'exists'
ELSE
create table ##load_file_log (id int identity(1,1),contents
varchar(1000))

select * from ##load_file_log
drop table ##load_file_log

If I change it to IS NULL, the same error occurs (Server: Msg 2714,
Level 16, State 1, Line 7
There is already an object named '##load_file_log' in the database.)

I have found one way to do it, but it seems a bit...clunky.

IF object_id('tempdb..##load_file_log') IS NULL
exec ('create table ##load_file_log (id int identity(1,1),contents
varchar(1000))')

I'll use that for now, but is there something I missed?
Thanks.

Use a permanent table and then you won't have to keep doing that.
Global temp tables are a waste of effort and don't have any special
advantages.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Erland Sommarskog]

M Bourgon (bo*****@gmail.com) writes:

I have two SPs, call them Daily and Weekly. Weekly will always call
Daily, but Daily can run on its own. I currently use a global temp
table because certain things I do with it won't work with a local temp
table or table variable.

I have been trying to get code so that if the table already exists, it
just keeps going and uses it, but creates the temp table if it doesn't
exist. Unfortunately, no matter how I try to do it, it always attempts
to create it, raising an error and breaking the code.

If first this had to do with recompiles, but it appears that if
you include CREATE TABLE statements for the same table twice in a
batch, the compilation fails. That's why it works when you put
the later CREATE TABLE statement in EXEC().

But I agree with David, a permanent table is probably better. Global
temp tables is a funny invention, and I very rarely find any use for them.

Not that I know what you are trying to do, but I have an article on my
web site that may give you some tips:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[M Bourgon]

But I agree with David, a permanent table is probably better. Global

temp tables is a funny invention, and I very rarely find any use for them.

For me, it's mostly a matter of not having to have _another_ random
table that I'll come back to in 5 years and wonder what's going on. We
have a bunch of those already, and I can't reuse one in case they run
simultaneously. The reason it's a global temp table is that so that if
it's running long, I can take a look. (Yes, I know I could do that if
it were a permanent table.)

Not that I know what you are trying to do, but I have an article on my
web site that may give you some tips:
http://www.sommarskog.se/share_data.html.

Thanks, Erland.

[Erland Sommarskog]

M Bourgon (bo*****@gmail.com) writes:

For me, it's mostly a matter of not having to have _another_ random
table that I'll come back to in 5 years and wonder what's going on. We
have a bunch of those already, and I can't reuse one in case they run
simultaneously. The reason it's a global temp table is that so that if
it's running long, I can take a look. (Yes, I know I could do that if
it were a permanent table.)

We have a bunch (20) of such tables in our system. They are also very
easy to identify, as we have a naming convention for them. In our case,
the convention is that the table table ends in -aid. You may pick another
convention, but the key is to have a way to tell from the name what kind
of table this is.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx