临时表既存在又不存在

Question

我试图在SQL Server中删除一个临时表，如果它存在，并创建一个新的临时表，如果它不存在。命令IF OBJECT_ID('tempdb.. #my_temp') IS NULL似乎无法确定临时表，然后当查询试图创建表时，我收到一个错误，即临时表已经存在。

这是我的查询:

IF OBJECT_ID('tempdb.. #my_temp') IS NOT NULL  
BEGIN 
DROP TABLE  #my_temp 
END
IF OBJECT_ID('tempdb.. #my_temp') IS NULL
BEGIN
print 'trying to create a new temp table'
CREATE TABLE   #my_temp
( [ID] int NOT NULL PRIMARY KEY CLUSTERED ,
) ON [PRIMARY]
END

如果我运行这个查询两次，第二次我收到这个输出:

trying to create a new temp table
Msg 2714, Level 16, State 6, Line 12
There is already an object named '#my_temp' in the database.

我的SQL Server是2019版供您参考。

Answer 1

删除…表名

IF OBJECT_ID('tempdb..#my_temp') IS NOT NULL  
BEGIN 
DROP TABLE  #my_temp 
END
IF OBJECT_ID('tempdb..#my_temp') IS NULL
BEGIN
print 'trying to create a new temp table'
CREATE TABLE   #my_temp
( [ID] int NOT NULL PRIMARY KEY CLUSTERED ,
) ON [PRIMARY]
END