Problem with Temporary Tables - 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

SQL Server Development (2000)

Problem with Temporary Tables

Author

Topic

Mmats
Starting Member

47 Posts

Posted - 2005-07-25 : 12:32:05

Why wont this parse?
CREATE TABLE #TEMP (Name varchar(400), Work int IDENTITY(1,1))
drop table #temp
CREATE TABLE #TEMP (Name varchar(400), Work int IDENTITY(1,1))

It gives me this error:
There is already an object named '#TEMP' in the database.

But there isnt an object already named #temp because this runs fine:
CREATE TABLE #TEMP (Name varchar(400), Work int IDENTITY(1,1))
drop table #temp

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 13:01:57

this is feature of parser, it can not know what are you doing in you script, so if it see that you're trying to create 2 tables with the same name - it raises error. To avoid it - split your script into 2 batches :

CREATE TABLE #TEMP (Name varchar(400), Work int IDENTITY(1,1))
drop table #temp
GO

CREATE TABLE #TEMP (Name varchar(400), Work int IDENTITY(1,1))