|
|
Posted by David Portas on 08/30/06 20:26
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|