SQL Server Drop temporal table returns does not remove in a single query run

yhived7q  于 2022-12-22  发布在  其他
关注(0)|答案(1)|浏览(156)

I generate a SQL query on an Excel file, so I have the same tables, selects, etc. With different data as:

CREATE TABLE #AddressOutputInserted ([AddressId] INT)

INSERT INTO [Addresses]([AddressLine1]...)
OUTPUT inserted.AddressId INTO #AddressOutputInserted([AddressId])
VALUES('Test address'...)

//some queries here

DROP TABLE #AddressOutputInserted

As you can see, I created a temp table, and at the end, I removed it, then it repeated the same thing; now I want to run them in the same query as:

BEGIN TRAN 

  BEGIN TRY

 CREATE TABLE #... DROP TABLE #
 CREATE TABLE #... DROP TABLE #
 CREATE TABLE #... DROP TABLE #

COMMIT TRANSACTION 

 END TRY
  BEGIN CATCH
      ROLLBACK TRAN
  END CATCH

When I run it throws an error:
There is already an object named '#AddressOutputInserted' in the database.
I tried this with table variable as: DECLARE @AddressOutputInserted AS TABLE(...) Also, with the temporal table as the example.
I noticed table variable cannot be disposed of in a single query run but the temporal table is not working; I thought the drop table should work, but it did not; how can I achieve this?

nsc4cvqm

nsc4cvqm1#

The solution was create the table on the first query, then remove records as:

CREATE TABLE #AddressOutputInserted ([AddressId] INT)

INSERT INTO [Addresses]([AddressLine1]...)
OUTPUT inserted.AddressId INTO #AddressOutputInserted([AddressId])
VALUES('Test address'...)

DELETE FROM #AddressOutputInserted

INSERT INTO [Addresses]([AddressLine1]...)
OUTPUT inserted.AddressId INTO 
etcetc..

相关问题