temp table problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

temp table problem

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2005-08-08 : 11:43:42

Hi,

We have a query batch like the following:
if object_id('tempdb..#tmp') is not null drop table #tmp
select * into #tmp from tab1
............................
This particular query batch can be executed once or more than 1 time in a single transaction. Problem is that when it is executed second time in the same transaction we get the error:
#tmp already exists...since this is normal behavior of sql server that we can't create the tmp table with same name in same transaction as it will do compile time name resolution for temp tables.
In this case we need to use the temp tables. How can we solve this issue?

Thanks
--Harvinder

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-08 : 12:49:43

try using this

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#tmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#tmp]

Kapil Arya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 01:55:55

Other method

If exists(select * from tempdb..#tmp)
Drop table #tmp

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-09 : 09:42:39

I may be wrong but I think Harvinder's issues isn't detecting and dropping the temp table. All the above methods should work fine for that. I think that his "batch" creates the same table twice and when the batch is prepared by sql server, it doesn't recognize that he is dropping the table before trying to re-create it so it errors out before execution is even attempted.

If that's the case, can you use a different name for the second instance of the table? Or isolate the two operations that use the same table name in their own batches? Or just create the table once, and use a truncate/insert to repopulate rather than a select into?

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-09 : 10:04:42

want to show us what you mean with an example?

Usually if that's the case, then I'd say you got a kludgy process

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

LinuxGold
Starting Member

3 Posts

Posted - 2005-08-15 : 07:51:32

I got similiar problem, I would like to drop table #tmp, whether it exists and ignore if it is invalid:


IF EXISTS(SELECT * FROM #TMP)
	DROP TABLE #TMP;
IF EXISTS(SELECT * FROM #TMPP)
	DROP TABLE #TMPP;
IF EXISTS(SELECT * FROM #TMPPP)
DROP TABLE #TMPPP;

[result]
Invalid object name '#TMP'.
[/result]

Power to people, Linux is here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources