August 21, 2018 at 1:48 pm
Hi gurus!
It is been a long time since I asked you for your input to clear my self doubt 🙂
I would like to know if a temp table created within a transaction will be dropped automatically after a COMMIT (I already know that ROLLBACK will take care of it) or should I issue a DROP statement after the COMMIT?
Here's my pseudo code:
BEGIN TRAN
BEGIN TRY
-- receive all records for today
CREATE TABLE #Temptable (RecID int, IncomingID int)
INSERT ProductionTable OUTPUT inserted.RecordID, CustomerID INTO #TempTable
SELECT c1, c2, c3, c4, c5
FROM IncomingDTSTable t1
WHERE MovementDate = GETDATE()
-- now, using our newly created RecIDs, add the details for each record
INSERT ProductionTable2
SELECT tbl1.RecID, tbl1.IncomingID, ctl.col1, ctl.col2, ctl.col3...etc
FROM #temptable tbl1
JOIN ProductionTableControl ctl on tbl1.RecID = ctl.c40
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()
ROLLBACK Transaction
END CATCH
So, should I leave it as is or must I put the Drop command after the COMMIT statement to get rid of #TempTable?
Your help is highly appreciated!
Regards,
JC
August 21, 2018 at 1:52 pm
A transaction commit will not drop a temp table. Technically, a rollback doesn't either. It just "undoes" the create. 🙂
A temp table is only dropped when the database session ends or when you explicitly drop it.
August 21, 2018 at 2:01 pm
A COMMIT will not drop the temp table. You need to drop it yourself.
A ROLLBACK will drop a temp table if it was created within the transaction, but not if it was created prior to the transaction.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 21, 2018 at 2:02 pm
Thank you, David and Scott
Yeah, I got that the rollback just undoes the create. 😉
Question answered!
Thank you, again and have a great day!
JCR
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply