There is already an object in the database with the name "#columntable"

Question

I am trying to execute 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 get an error

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

Can someone tell me why? The same query works fine unless I write the else part.

Answer 1

Temp tables are not automatically dropped at the end of a query only when the current database connection is deleted or you explicitly delete them using DROP TABLE #columntable

Either check the table at the beginning of the query, or alwayas delete it at the end (preferably both)

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

To test this, I split your request 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 pleased with this. Interestingly, if you switch to using non-temp tables, the original query will understand perfectly (I understand the problems that would create, I was just interested to find out why the query would not be analyzed).

Answer 2

Unfortunately, this is a SQL Server Analyzer error ( confirmed by Microsoft ).

@DizGrizz is also right - SELECT .. INTO #SomeTable does not work if repeated in IF .. ELSE .

IF .. ELSE .. CREATE TABLE #SomeTempTable

In response to the actual question, it is created and then changes the work table (you also only need to check and delete it 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

However, the problem was the same as @DizGrizz: IF .. ELSE combined with SELECT .. INTO #SomeTable failed. As a workaround, you can select the top 0 rows (i.e. None) to create a table with the correct column types. (This isolates script type changes from the column type, and also avoids the pain of declaring each type.) INSERT INTO can be used if the IDENTITY_INSERT parameter 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 3

This also happens if you create tables using 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 try to detect this, because in many cases the parser will not be able to determine if the table will already exist. The above code is a perfect example ... for the analyzer, it would not be possible to determine the value of @MyVariable. I hope someone informed MS about this error (I don't have an ear).

Answer 4

Use global temporary tables and wrap the selection in exec.

Example:

Crash

 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 trick a buggy parser that is trying to be smarter than it is. And Microsoft - please fix it.

Answer 5

The error is incorrect, remove the if option and it goes through a fine. So the problem is the existence of:

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

Answer 6

You can check if this exists:

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

Answer 7

Well, I got the answer ... As Martin said, this is a parsing / compilation 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 it worked for me.