SQL 删除临时表（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7259285/
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 a temporary table if it exists
提问by user710502
I have two lines of code in SQL that create two tables on the fly, i need to do something like
我在 SQL 中有两行代码可以动态创建两个表，我需要做一些类似的事情
IF TABLE EXISTS
DROP IT AND CREATE IT AGAIN
ELSE
CREATE IT
my lines are the following ones
我的台词如下
CREATE TABLE ##CLIENTS_KEYWORD(client_id int)
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)
how can I apply that concept for these two tables in my procedure?
我如何在我的程序中为这两个表应用这个概念？
回答by Martin Smith
From SQL Server 2016 you can just use
从 SQL Server 2016 开始，您可以使用
DROP TABLE IF EXISTS ##CLIENTS_KEYWORD
On previous versions you can use
在以前的版本上，您可以使用
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE ##CLIENTS_KEYWORD
CREATE TABLE ##CLIENTS_KEYWORD
(
client_id INT
)
You could also consider truncating the table instead rather than dropping and recreating.
您也可以考虑截断表而不是删除和重新创建。
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD', 'U') IS NOT NULL
TRUNCATE TABLE ##CLIENTS_KEYWORD
ELSE
CREATE TABLE ##CLIENTS_KEYWORD
(
client_id INT
)
回答by Derek Kromm
Check for the existence by retrieving its object_id:
通过检索其 object_id 来检查是否存在：
if object_id('tempdb..##clients_keyword') is not null
drop table ##clients_keyword
回答by WonderWorker
What you asked for is:
你要求的是：
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD') IS NOT NULL
BEGIN
DROP TABLE ##CLIENTS_KEYWORD
CREATE TABLE ##CLIENTS_KEYWORD(client_id int)
END
ELSE
CREATE TABLE ##CLIENTS_KEYWORD(client_id int)
IF OBJECT_ID('tempdb..##TEMP_CLIENTS_KEYWORD') IS NOT NULL
BEGIN
DROP TABLE ##TEMP_CLIENTS_KEYWORD
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)
END
ELSE
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)
Since you're always going to create the table, regardless of whether the table is deleted or not; a slightly optimised solution is:
因为你总是要创建表，不管表是否被删除；稍微优化的解决方案是：
IF OBJECT_ID('tempdb..##CLIENTS_KEYWORD') IS NOT NULL
DROP TABLE ##CLIENTS_KEYWORD
CREATE TABLE ##CLIENTS_KEYWORD(client_id int)
IF OBJECT_ID('tempdb..##TEMP_CLIENTS_KEYWORD') IS NOT NULL
DROP TABLE ##TEMP_CLIENTS_KEYWORD
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)
