为什么不能在一个事务中创建和删除两个表呢?

This will throw the error

这将抛出错误

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

数据库中已经有一个名为“##TempComment”的对象。

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.

注释是我的集合，只有满足条件时，我才取临时表的值。我有20个销售在评估和一个字段检查是否销售已经被选择，只有当我知道这是真实的，我采取临时价值数据。

1 个解决方案

#1

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.

我们返回错误，Msg 3701，第11级，状态5，第1行不能删除表“#t”，因为它不存在，或者您没有权限。

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.

现在，删除#t，删除表#t。我们不能删除表#t，因为它不存在或者你没有权限。我们被告知无法创建表#t，因为它已经存在，即使它从未创建过。

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.

SQL Server解析器正在查看这两个create语句，不考虑删除语句，并在执行任何实际工作之前决定将发生错误。SQL Server只使用temp表，创建永久表的drop创建工作。

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)
    )');

#1