There is already an object named '#columntable

Question

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.

Answer 1

The error is wrong, remove the if clause and it runs through fine. Thus the problem is in the exists:

if object_id('tempdb..#columntable') is not null
begin
    drop table #columntable
end 

create table #columntable (oldcolumns varchar(100))

Answer 2

Use global temp tables and wrap the select in exec.

Example:

Fail

declare @a int = 1
if object_id('tempdb..##temp') is not null drop table ##temp
if(@a = 1) select * into ##temp from table_1
else if(@a = 2) select * into ##temp from table_2

Win

declare @a int = 1
if object_id('tempdb..##temp') is not null drop table ##temp
if(@a = 1) exec('select * into ##temp from table_1')
else if(@a = 2) exec('select * into ##temp from table_2')

This will fool the buggy parser that is trying to be smarter than it is. And Microsoft - please fix this.

Answer 3

This is a SQL Server parser error unfortunately (confirmed by Microsoft).

@DizGrizz is also right - SELECT .. INTO #SomeTable doesn't work if repeated in IF .. ELSE statements.

IF .. ELSE .. CREATE TABLE #SomeTempTable

In answer to the actual question, creating then altering the table works (you also only have to check and drop once)...

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
BEGIN 
     DROP TABLE #MyTempTable
END  

CREATE TABLE #MyTempTable (DummyColumn BIT)

IF EXISTS (SELECT 1 FROM EMP WHERE EID = 6)     
    BEGIN
        ALTER TABLE #MyTempTable
        ADD MyColumnType1 VARCHAR(100)

        ALTER TABLE #MyTempTable
        DROP COLUMN DummyColumn
    END
ELSE
    BEGIN
        ALTER TABLE #MyTempTable 
        ADD MyColumnType2 VARCHAR(100)

        ALTER TABLE #MyTempTable
        DROP COLUMN DummyColumn
    END

IF .. ELSE .. SELECT INTO #SomeTempTable

The issue I had however was the same as @DizGrizz: IF .. ELSE combined with SELECT .. INTO #SomeTable fails. As a workaround it's possible to select the top 0 rows (i.e. none) to create the table with the correct column types. (This insulates the script from column type changes and also avoids the pain of declaring every type.) INSERT INTO can then be used, provided IDENTITY_INSERT is set to ON to prevent errors:

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
    DROP TABLE #MyTempTable

-- This creates the table, but avoids having to declare any column types or sizes
SELECT TOP 0 KeyNm
INTO #MyTempTable
FROM dbo.MyDataTable2

-- Required to prevent IDENTITY_INSERT error
SET IDENTITY_INSERT #MyTempTable ON

IF @something = 1
    BEGIN
        -- Insert the actual rows required into the (currently empty) temp table
        INSERT INTO #MyTempTable (KeyNm)
        SELECT KeyNm
        FROM dbo.MyDataTable2
        WHERE CatNum = 2
    END
ELSE
    BEGIN
        -- Insert the actual rows required into the temp table
        INSERT INTO #MyTempTable (KeyNm)
        SELECT KeyNm
        FROM dbo.MyDataTable2
        WHERE CatNum = 8
    END

SET IDENTITY_INSERT #MyTempTable OFF

Answer 4

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).

Answer 5

You can check if it exists by doing:

IF OBJECT_ID('tempdb..#columntable') IS NOT NULL
BEGIN
DROP TABLE #columntable
PRINT 'Dropped table...'
END

Answer 6

Well I got the answer... As Martin said this is a parse/compile issue. So I Tried changing my script as below

  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
  go
 if exists (select 1  from emp where eid = 1)   
     begin
        if object_id('tempdb..#columntable') is not null 
          begin 
             drop table #columntable            
          end         
        create table #columntable (newcolumns varchar(100))   
     end

And this worked for me.