时间戳

首页 > 解决方案 > 如何删除临时表

sql-server - 如何删除临时表

问题描述

使用 SELECT .. INTO 语句创建临时表

SELECT *
    INTO #MyTempTable
FROM ...

在数据库、tempdb、临时表下，我看到了临时表 dbo.#MyTempTable______0000000016CA

现在我想放下桌子。我尝试了以下方法：

DROP TABLE IF EXISTS #MyTempTable

和

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
BEGIN 
    DROP TABLE #MyTempTable 
END

两者都没有删除表

选择返回 NULL：

SELECT OBJECT_ID('tempdb..#MyTempTable')

标签： sql-serversql-server-2016

解决方案

Pay attention to the name of checked table and dropped table.. #MyTempTable vs #lu_sensor_name_19

This is working for me:

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTable;

if this does not work for you I think your problem is related to scope and/or visibility of your temp table.

temp tables exists only with the session where they were created, I guess you are dropping a temp table that doesn't exists amymore, or a temp table in another session.

first you have to check if the session in which you make the SELECT INTO is still active when you want to drop the table.

if answer is yes, then check if you are dropping from the same session or from another one.

in the second case you can solve the problem using ##MyTempTable instead of #MyTempTable

otherwise it means that something or someone else have already dropped it

推荐阅读