Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
    
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

1 Answer 1

up vote 7 down vote accepted

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

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.

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: http://stackoverflow.com/questions/9812302/can-i-recreate-a-temp-table-after-dropping-it/9812563#9812563 -- jcolebrand

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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