在创建临时表之前检查是否存在临时表，并删除是否存在 [英] 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屋！