on May 3, 2017
Every now and again, I need use a global temporary table for some testing or demo code.
Each time I do, I stumble a little bit when it comes to checking for the existence of the global temp table, in order to make my code re-runnable.
Here’s the TSQL to check for the object_id of a global temporary table:
SELECT OBJECT_ID('tempdb..##Temp');
GO
So if you’d like to drop the global temp table if it exists, you can do this:
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
DROP TABLE ##Temp;
GO
Or if you’d like to truncate it if it exists, and create it if it doesn’t exist, you can do this:
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL
TRUNCATE TABLE ##Temp;
ELSE
CREATE TABLE ##Temp (
RecordID INT IDENTITY(1,1),
CharColumn CHAR(500) NOT NULL,
CONSTRAINT pk_UserDatabaseTablePK PRIMARY KEY CLUSTERED (RecordID)
)
GO