Menu
When writing a T-SQL script that I plan on re-running, often times I use temporary tables to store temporary data. Since the temp table is created on the fly, I'd like to be able to drop that table only if it exists (before I create it).
I'll post the method that I use, but I'd like to see if there is a better way.
743
In SQL Server, we can use the OBJECT_ID function to get the table name of the temporary table, and if the table is found, we can use the DROP TABLE statement to drop the temp table in sql.
Temporary tables can have a Time Travel retention period of 1 day; however, a temporary table is purged once the session (in which the table was created) ends so the actual retention period is for 24 hours or the remainder of the session, whichever is shorter.
IF Object_Id('TempDB..#TempTable') IS NOT NULL BEGIN DROP TABLE #TempTable END The OBJECT_ID function returns the internal object id for the given object name and type. 'tempdb..#t1' refers to the table #t1 in the tempdb database. 'U' is for user-defined table.
IF OBJECT_ID('tempdb..#t1', 'U') IS NOT NULL DROP TABLE #t1 CREATE TABLE #t1 ( id INT IDENTITY(1,1), msg VARCHAR(255) ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With