Search code examples
sqlsql-server-2005t-sql

There is already an object named '#columntable' in the database

I am trying the following query

   if exists (select 1  from emp where eid = 6)     
     begin
        if object_id('tempdb..#columntable') is not null 
          begin 
             drop table #columntable                         
          end                     
        create table #columntable (oldcolumns varchar(100))   
     end
  else
     begin
        if object_id('tempdb..#columntable') is not null 
          begin 
             drop table #columntable            
          end     


    create table #columntable (newcolumns varchar(100))   
 end

But I am getting the error 

Msg 2714, Level 16, State 1, Line 8
There is already an object named '#columntable' in the database.

Can anyone suggest why? The same query works fine if I do not write the else part.

Solution

  • Temp tables are not dropped automatically at the end of a query, only when the current connection to the DB is dropped or you explicitly delete them with DROP TABLE #columntable

    Either test for the existence of the table at the start of the query or alwayas delete it at the end (preferably both)

    EDIT: As Matrin said in his comment, this is actually a parse error. You get the same error if you only parse the SQL as when you execute it.

    To test that out I split up your query and tried:

    if exists (select 1 from emp where id = 6)
  create table #columntable (newcolumns varchar(100))
GO
if not exists (select 1 from emp where id = 6)
  create table #columntable (oldcolumns varchar(100))
GO

    The parser is happy with that. Interestingly if you change to using non-temp tables the original query parses fine (I realise the problems that would create, I was just interested to find out why the query would not parse).