Drop temporal table returns does not remove in a single query run-CodePudding

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?

CodePudding user response：

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