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?
1条答案
按热度按时间nsc4cvqm1#
The solution was create the table on the first query, then remove records as: