如果存在临时表，则删除它

我有两行代码在 SQL，创建两个表的动态，我需要做一些类似

IF TABLE EXISTS
DROP IT AND CREATE IT AGAIN
ELSE
CREATE IT

我的台词是这样的

CREATE TABLE ##CLIENTS_KEYWORD(client_id int)
CREATE TABLE ##TEMP_CLIENTS_KEYWORD(client_id int)

在我的过程中，如何对这两个表应用这个概念？

450204 次浏览
小开
最佳答案

From SQL Server 2016 you can just use

 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
)
小开

Check for the existence by retrieving its object_id:

if object_id('tempdb..##clients_keyword') is not null
drop table ##clients_keyword
小开

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)

提交答案