创建临时表前先检查临时表是否存在，如果存在则删除 [英] Check if a temporary table exists and delete if it exists before creating a temporary table

问题描述

我正在使用以下代码检查临时表是否存在并在再次创建之前删除该表是否存在.只要我不更改列，它就可以正常工作.如果我稍后添加一列，它会给出一个错误提示无效的列".请让我知道我做错了什么.

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give an error saying "invalid column". Please let me know what I am doing wrong.

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

select company, stepid, fieldid from #Results

--Works fine to this point

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

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

select company, stepid, fieldid, NewColumn from #Results

--Does not work

推荐答案

我无法重现该错误.

也许我不明白这个问题.

Perhaps I'm not understanding the problem.

以下在 SQL Server 2005 中对我来说很好用，额外的foo"列出现在第二个选择结果中:

The following works fine for me in SQL Server 2005, with the extra "foo" column appearing in the second select result:

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

这篇关于创建临时表前先检查临时表是否存在，如果存在则删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！