Why can't you create and drop a table twice in one transaction?

Question

This will throw the error

There is already an Object named '##TempComment' in the Database.

DROP TABLE ##TempComment
CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);
DROP TABLE ##TempComment
CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);

The above is a simple way of putting the problem, but below you can see why i am doing this way.

I am aware this is an odd request but here is more on what i am trying to achieve.

  DECLARE @ValuationId INT = 20897
  DECLARE @Count INT = 0
  DECLARE @isCompSale NVARCHAR(MAX)
  DECLARE @Comment nvarchar(250)

    DROP TABLE ##TempComment
    SELECT TagValue
    INTO ##TempComment
    FROM [FormValueLive_sql].[dbo].[ValuationDetail]
    WHERE TagName IN ('sale_1_erf','sale_1_portion','sale_1_township', 'comparable_sale_1_sales_price', 'comparable_sale_1_sales_date', 'sale_1_overall') AND ValuationId = @ValuationId
    SET @isCompSale = (SELECT TagValue FROM [FormValueLive_sql].[dbo].[ValuationDetail] WHERE TagName = 'sale_1_use_as_comparable' AND ValuationId = @ValuationId)

    IF @isCompSale = 'Yes' AND @Count < 3
    Begin
    SELECT @Comment = COALESCE(@Comment + ',','') + TagValue FROM ##TempComment
    SET @Count = @Count + 1
    END

    SET @isCompSale = 'No'

 --So Comments is my collective and only if a condition is met do i take the values of the temp table.

    DROP TABLE ##TempComment
    SELECT TagValue
    INTO ##TempComment
    FROM [FormValueLive_sql].[dbo].[ValuationDetail]
    WHERE TagName IN ('sale_6_erf','sale_6_portion','sale_6_township', 'comparable_sale_6_sales_price', 'comparable_sale_6_sales_date', 'sale_6_overall') AND ValuationId = @ValuationId

    SET @isCompSale = (SELECT TagValue FROM [FormValueLive_sql].[dbo].[ValuationDetail] WHERE TagName = 'sale_6_use_as_comparable' AND ValuationId = @ValuationId)

    IF @isCompSale = 'Yes' AND @Count < 3
    Begin
    SELECT @Comment = COALESCE(@Comment + ', ','') + TagValue FROM ##TempComment
    SET @Count = @Count + 1
    END

    SELECT @Comment

So Comments is my collective and only if a condition is met do i take the values of the temp table. I have 20 sales in a valuation and one of the fields checks if the sale has been selected only if i know that to be true do i take the temp value data.

Can't you just use insert into and truncate instead of create and drop? — James Z, Oct 29 '15 at 14:47
Why not just truncate it instead of dropping and recreating it? Also be careful using global temp tables, they have a tendency to produce some strange errors due to concurrency. — Sean Lange, Oct 29 '15 at 14:48
Check for the existence of the global temp table before you drop it. — Adish, Oct 29 '15 at 14:48
@JamesZ The insert into is creating the table. The second bit of code is all the sql as i have it at the moment, as you can see i don't create the table. Maybe i am inserting into wrong that it creates the table? — Pomster, Oct 29 '15 at 14:49
Select into creates the table, insert into ##TempComment select ... does not, it works only if the table exists already — James Z, Oct 29 '15 at 14:51
@JamesZ Ah ... ok let me give that a try. .. I am not able to INSERT value INTO ? — Pomster, Oct 29 '15 at 14:53
INSERT syntax cannot have WHERE clause. The only time you will find INSERT has WHERE clause is when you are using INSERT INTO...SELECT statement. http://stackoverflow.com/q/12948554/1356321 — Pomster, Oct 29 '15 at 14:55
Looking at your code, I don't think you need temp tables at all. You could populate `@Comment` directly from the permanent table. — Tab Alleman, Oct 29 '15 at 15:02
I could create a temp table for each sale and then drop it, but then i would have 20 creating and dropping in one transaction. Think i will have to definitely change it to local temp for this. — Pomster, Oct 29 '15 at 15:02
For the Inserts you have to be cognizant of the existence of the table. If a table does not exist you can use SELECT...INTO...WHERE if it does exist you have to use INSERT INTO...SELECT...WHERE. To keep both patterns similar and garuantee that the scheme stays similar you'll want to create/declare the table up front with a fixed schema and then use the INSERT INTO pattern and delete/truncate when appropriate. — Brad D, Oct 29 '15 at 15:04

score 4 · Accepted Answer · answered Oct 29 '15 at 15:07

TL;DR The parser is giving the error and not running any command in the batch.

Why can't you create and drop a table twice in one transaction? You can. The problem is doing two creates in the same batch.

One transaction, two batches: (works)

BEGIN TRANSACTION

CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);
GO
DROP TABLE ##TempComment
CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);
COMMIT TRANSACTION

Two transactions, one batch: (No Works.)

BEGIN TRANSACTION
CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);
COMMIT TRANSACTION
DROP TABLE ##TempComment
CREATE TABLE ##TempComment
( 
    TagValue NvarChar(MAX)
);
COMMIT TRANSACTION
GO

Next piece of evidence, new table name just to be clean:

CREATE TABLE #t (c INT)
DROP TABLE #t
CREATE TABLE #t (c INT)

We get back the error, Msg 3701, Level 11, State 5, Line 1 Cannot drop the table '#t', because it does not exist or you do not have permission.

Now, drop #t, DROP TABLE #t. and we get, Cannot drop the table '#t', because it does not exist or you do not have permission. We were told that table #t could not be created because it was already existed even though it was never created.

The SQL Server parser is seeing the two create statements, not considering the drop and deciding that an error is going to happen before doing any actual work. SQL Server only does this with temp tables, create drop create of permanent tables works.

I don't understand your use case, and think a global temporary table is probably the wrong choice. However, you can get the effect you want by putting the creates in a string and running them dynamically, that will place the parsing into separate batches.

EXEC ('CREATE TABLE ##TempComment
    ( 
        TagValue NvarChar(MAX)
    )');
DROP TABLE ##TempComment
EXEC ('CREATE TABLE ##TempComment
    ( 
        TagValue NvarChar(MAX)
    )');

It is mentioned in the documentation for CREATE TABLE: *If more than one temporary table is created inside a single stored procedure or batch, they must have different names.* https://msdn.microsoft.com/en-us/library/ms174979.aspx — Peter Henell, Oct 29 '15 at 15:18

Why can't you create and drop a table twice in one transaction?

1 Answers1