CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

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.

Upvotes: 11

Views: 38931

Answers (9)

RoastBeast
RoastBeast

Reputation: 1115

In my case I know that the schema will be consistent in either scenario, so I pulled from the live table first, and if a certain condition is met, I delete from the temporary table and populate with data from the test table, thereby avoiding an ELSE clause and a second INSERT INTO statement of the IF block:

DROP TABLE IF EXISTS #t

SELECT *
INTO #t
FROM tableOfData

IF @pullFromTestTableInstead = 1
BEGIN
    DELETE FROM #t

    INSERT INTO #t
    SELECT *
    FROM tableOfData_TEST
END

Upvotes: 0

Taz Poltorak
Taz Poltorak

Reputation: 1

I have been experiencing this issue. My query consisted of several joined SELECT statements in the form of:

DROP TABLE IF EXISTS ##TempTableName

SELECT statement ...

So, every time I tried to alter SQL code I would get the above error. I have changed all my temp tables from ##global to #local and now I am able to alter my SQL as many times as needed. So the example above would become:

DROP TABLE IF EXISTS #TempTableName

SELECT statement ...

Upvotes: 0

SharpC
SharpC

Reputation: 7454

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

Upvotes: 9

Eric Hartford
Eric Hartford

Reputation: 17962

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.

Upvotes: 1

DizGrizz
DizGrizz

Reputation: 21

This also occurs if you create the tables with SELECT INTO...as in

IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
    DROP TABLE #MyTempTable
SELECT TOP 1 @MyVariable = ScaleValue
    FROM MyDataTable1
    WHERE ProductWeight > 1000
IF @MyVariable = 1
BEGIN
    SELECT KeyNm
        INTO #MyTempTable
        FROM dbo.MyDataTable2
        WHERE CatNum = 2
END
ELSE
BEGIN
    SELECT KeyNm
        INTO #MyTempTable
        FROM dbo.MyDataTable2
        WHERE CatNum = 8
END

The parser should not even attempt to detect this because, in many cases, it would be impossible for the parser to determine if the table would already exist. The code above is a perfect example...there would be no way for the parser to determine the value of @MyVariable. I hope that someone has informed MS of this bug (I don't have their ear).

Upvotes: 2

Mohammad Nadeem
Mohammad Nadeem

Reputation: 9392

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.

Upvotes: 0

Tony
Tony

Reputation: 10327

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

Upvotes: 7

Jens
Jens

Reputation: 3299

You can check if it exists by doing:

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

Upvotes: 1

Joel Mansford
Joel Mansford

Reputation: 1316

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

Upvotes: 0

Related Questions