SQL Server 2005 error

Question

Why can't you do this and is there are work around?

You get this error.

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

declare @x int

set @x = 1

if (@x = 0)
begin
    select 1 as Value into #temptable
end
else
begin
   select 2 as Value into #temptable
end

select * from #temptable

drop table #temptable

Answer 1

I am going to guess that the issue is that you haven't created the #temptable.

Sorry I can't be more detailed but since you haven't even tried to explain what you are seeing you get a less than stellar answer.

Answer 2

wait...are you getting an error on this?
let's see it if you are.

Answer 3

You can't do that because of deferred name resolution, you can do it with a real table, just take out the pound signs

You could also create the temp table first on top and then do a regular insert into table

Answer 4

From the look of the code is seems like you might have been prototyping this in SQL Studio or similiar, right? Can I guess that you've run this a few times and had it get to the point where it's created #temptable but then failed before it got to the end and dropped the table again? Restart the SQL editing tool you're using and try again.

Answer 5

First step... check if the table already exists... if it does, delete it. Next, explicitly create the table rather than using SELECT INTO...

You'll find it much more reliable that way.

IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL 
BEGIN
DROP TABLE #temptable
END

CREATE TABLE #temptable (Value INT)

declare @x int

set @x = 1

if (@x = 0)
begin
    INSERT INTO #temptable (Value) select 1
end
else
begin
    INSERT INTO #temptable (Value) select 2
end

select * from #temptable

drop table #temptable

Also, hopefully the table and field names are simplified for your example and aren't what you really call them ;)

-- Kevin Fairchild

Answer 6

Deferred name resolution is also the reason you cannot be sure that sp_depends gives back correct results, check out this post I wrote a while back Do you depend on sp_depends (no pun intended)

Answer 7

This is a two-part question and while Kev Fairchild provides a good answer to the second question he totally ignores the first - why is the error produced?

The answer lies in the way the preprocessor works. This

SELECT field-list INTO #symbol ...

is resolved into a parse-tree that is directly equivalent to

DECLARE #symbol_sessionid TABLE(field-list)
INSERT INTO #symbol_sessionid SELECT field-list ...

and this puts #symbol into the local scope's name table. The business with _sessionid is to provide each user session with a private namespace; if you specify two hashes (##symbol) this behaviour is suppressed. Munging and unmunging of the sessionid extension is (ovbiously) transparent.

The upshot of all this is that multiple INTO #symbol clauses produce multiple declarations in the same scope, leading to Msg 2714.