8

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:

CREATE TABLE #WidgetSetting 
(
    WidgetID bigint not null,
    Name nvarchar(100) not null,
    Value nvarchar(max) not null,
    CreateDate datetime not null
)

INSERT VALUES

MERGE TABLES

DROP TABLE #WidgetSetting

But now SSMS is complaining that the object already exists by the next time the CREATE TABLE fires. What gives?

I think it's obvious I'm going to have to declare the table once at the beginning of the script, truncate instead of drop, but it's frustrating, naturally, to not be able to just drop the table and use the same name again.

  • As Aaron commented below, if this is the route you've taken, the easiest fix would likely be to drop the #TABLE at the very end of the script and simply TRUNCATE it in the steps between. That's assuming it's not possible to just redesign the script to work differently in the first place. :) – Kahn May 21 '13 at 8:40
  • 1
    That's actually what I did to resolve it. I was just confused by the behavior. He explained the why which is what I wanted more so than the how to fix it. – jcolebrand May 21 '13 at 15:47
11

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END

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

And to prove it's not SSMS complaining at compile time (a common misconception):

DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END';

EXEC sp_executesql @sql;

Yields the exact same error, even though SSMS does not try to parse or validate dynamic SQL before sending it to the server via sp_executesql.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.