SQL 删除临时表（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25891416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop temp table if it exists
提问by Alan Ford
Friends,
朋友们，
I am creating a temp table. The script may be run several times so I need to check if the temp table exist then drop it. I have the written the code below but I get an error when running the script twice, that the table already exists:
我正在创建一个临时表。该脚本可能会运行多次，因此我需要检查临时表是否存在，然后将其删除。我写了下面的代码，但是在运行脚本两次时出现错误，表已经存在：
There is already an object named '#lu_sensor_name_19' in the database.
数据库中已经有一个名为“#lu_sensor_name_19”的对象。
It appears that IF OBJECT_ID('alarm..#lu_sensor_name_19') IS NOT NULLdoes not return true when the tablle is not null. What am I doing wrong?
IF OBJECT_ID('alarm..#lu_sensor_name_19') IS NOT NULL当表格不为空时，它似乎不会返回 true。我究竟做错了什么？
IF OBJECT_ID('alarm..#lu_sensor_name_19') IS NOT NULL
BEGIN
DROP TABLE #lu_sensor_name_19
END
CREATE TABLE #lu_sensor_name_19(
sensorname_id int NOT NULL,
sensorname nvarchar(50) NOT NULL,
paneltype_id smallint NOT NULL,
panel_version_id int NULL,
prefix_allowed tinyint NOT NULL,
base_allowed tinyint NOT NULL,
suffix_allowed tinyint NOT NULL,
key_value int NULL,
sort_index int NULL,
device_allowed tinyint NOT NULL,
sensor_name_group_id smallint NOT NULL,
)
回答by Paul Williams
Temp #Tables are created in tempdb. Try this:
Temp #Tables 是在 tempdb 中创建的。尝试这个：
IF OBJECT_ID('tempdb..#lu_sensor_name_19') IS NOT NULL
BEGIN
DROP TABLE #lu_sensor_name_19
END
CREATE TABLE #lu_sensor_name_19...
SQL Server 2016 added the ability to do the drop in one line:
SQL Server 2016 添加了在一行中进行删除的功能：
DROP TABLE IF EXISTS #lu_sensor_name_19
CREATE TABLE #lu_sensor_name_19...
回答by John Zabroski
Use this.
用这个。
IF OBJECT_ID('tempdb.dbo.##myTempTable', 'U') IS NOT NULL
BEGIN
DROP TABLE ##myTempTable;
--DROP TABLE ##tempdb.dbo.myTempTable;
/* Above line commented out, because it generates warning:
"Database name 'tempdb' ignored, referencing object in tempdb.",
which is a pain in the neck if you are using a temp table to generate SQL code,
and want to print the code to the screen.*/
END;
GO
CREATE TABLE ##myTempTable(
FooBar nvarchar(128) not null,
);
And, in SQL Server 2016, you can write:
而且，在 SQL Server 2016 中，您可以编写：
DROP TABLE IF EXISTS ##myTempTable
