当前位置： 文江博客话题详情

创建临时表前先检查临时表是否存在，如果存在则删除

发布于 2024-07-15 02:14:39 字数 723 浏览 13 评论 0原文

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

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
原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（17）

疏忽 2024-07-22 02:14:39

我无法重现该错误。

也许我不明白这个问题。

以下内容在 SQL Server 2005 中对我来说效果很好，第二个选择结果中出现了额外的“foo”列：

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

I cannot reproduce the error.

Perhaps I'm not understanding the problem.

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
回复 原文
留蓝 2024-07-22 02:14:39

您可以截断并重用它，而不是删除并重新创建临时表

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    Truncate TABLE #Results
else
    CREATE TABLE #Results
    (
        Company             CHAR(3),
        StepId              TINYINT,
        FieldId             TINYINT,
    )

如果您使用的是 Sql Server 2016 或 Azure Sql， 数据库 然后使用以下语法删除临时表并重新创建它。 更多信息请参见此处 MSDN

语法

删除表[如果存在][数据库名称。 [架构名称]。 |
架构名称。 ] 表名 [ ,...n ]

查询：

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

Instead of dropping and re-creating the temp table you can truncate and reuse it

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    Truncate TABLE #Results
else
    CREATE TABLE #Results
    (
        Company             CHAR(3),
        StepId              TINYINT,
        FieldId             TINYINT,
    )

If you are using Sql Server 2016 or Azure Sql Database then use the below syntax to drop the temp table and recreate it. More info here MSDN

Syntax

DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . |
schema_name . ] table_name [ ,...n ]

Query:

DROP TABLE IF EXISTS #Results
CREATE TABLE #Results
  (
   Company             CHAR(3),
   StepId              TINYINT,
   FieldId             TINYINT,
  )
回复 原文
就像说晚安 2024-07-22 02:14:39

该语句的顺序应

  1. 为表
  2. GO
  3. Select 语句的 Alter 语句。

如果中间没有“GO”，整个脚本将被视为一个脚本，当 select 语句查找该列时，将找不到该列。

使用 'GO' 时，它将把 'GO' 之前的脚本部分视为一个批次，并在进入 'GO' 之后的查询之前执行。

The statement should be of the order

  1. Alter statement for the table
  2. GO
  3. Select statement.

Without 'GO' in between, the whole thing will be considered as one single script and when the select statement looks for the column,it won't be found.

With 'GO' , it will consider the part of the script up to 'GO' as one single batch and will execute before getting into the query after 'GO'.

回复 原文
故人的歌 2024-07-22 02:14:39

这可以通过一行代码来完成：

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

This could be accomplished with a single line of code:

IF OBJECT_ID('tempdb..#tempTableName') IS NOT NULL DROP TABLE #tempTableName;
回复 原文
陈年往事 2024-07-22 02:14:39

我认为问题是你需要在中间添加 GO 语句来将执行分成批次。 由于第二个删除脚本，即 IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results 并未删除属于单个批次的临时表。 您可以尝试下面的脚本吗？

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

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

GO

select company, stepid, fieldid from #Results

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

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

GO

select company, stepid, fieldid, NewColumn from #Results

I think the problem is you need to add GO statement in between to separate the execution into batches. As the second drop script i.e. IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results did not drop the temp table being part of single batch. Can you please try the below script.

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

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

GO

select company, stepid, fieldid from #Results

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

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

GO

select company, stepid, fieldid, NewColumn from #Results
回复 原文
那支青花 2024-07-22 02:14:39

现在，如果您使用的是新版本的 SQL Server (2016+) 之一，则可以使用以下语法。

DROP TABLE IF EXISTS schema.yourtable(even temporary tables #...)

Now you can use the below syntax if you are using one of the new versions of SQL Server (2016+).

DROP TABLE IF EXISTS schema.yourtable(even temporary tables #...)
回复 原文
謌踐踏愛綪 2024-07-22 02:14:39

这对我有用：
social.msdn.microsoft.com/Forums/ en/transactsql/thread/02c6da90-954d-487d-a823-e24b891ec1b0?prof=必需

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

   and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;

This worked for me:
social.msdn.microsoft.com/Forums/en/transactsql/thread/02c6da90-954d-487d-a823-e24b891ec1b0?prof=required

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

   and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;
回复 原文
寂寞清仓 2024-07-22 02:14:39

由于 OBJECT_ID 对我不起作用，所以我只是发表一点评论。 它总是返回

`#tempTable 不存在

..即使它确实存在。 我刚刚发现它以不同的名称存储（后缀 _ 下划线），如下所示：

#tempTable________

这对我来说效果很好：

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#tempTable%') BEGIN
   DROP TABLE #tempTable;
END;

Just a little comment from my side since the OBJECT_ID doesn't work for me. It always returns that

`#tempTable doesn't exist

..even though it does exist. I just found it's stored with different name (postfixed by _ underscores) like so :

#tempTable________

This works well for me:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#tempTable%') BEGIN
   DROP TABLE #tempTable;
END;
回复 原文
青芜 2024-07-22 02:14:39

这对我有用，

IF OBJECT_ID('tempdb.dbo.#tempTable') IS NOT NULL 
DROP TABLE #tempTable;

这里 tempdb.dbo（dbo 只是你的模式）更重要。

This worked for me,

IF OBJECT_ID('tempdb.dbo.#tempTable') IS NOT NULL 
DROP TABLE #tempTable;

Here tempdb.dbo(dbo is nothing but your schema) is having more importance.

回复 原文
笛声青案梦长安 2024-07-22 02:14:39

pmac72 使用 GO 将查询分解为批次并使用 ALTER。

您似乎正在运行相同的批次，但在更改后运行了两次： DROP... CREATE... edit... DROP... CREATE ..

也许发布您的准确代码，以便我们可以看看发生了什么事。

pmac72 is using GO to break down the query into batches and using an ALTER.

You appear to be running the same batch but running it twice after changing it: DROP... CREATE... edit... DROP... CREATE..

Perhaps post your exact code so we can see what is going on.

回复 原文
第七度阳光i 2024-07-22 02:14:39

注意：这也适用于 ## 临时表。

即

IF OBJECT_ID('tempdb.dbo.##AuditLogTempTable1', 'U') IS NOT NULL
DROP TABLE ##AuditLogTempTable1

注意：此类命令仅适用于 SQL Server 2016 之后的版本。
问问自己.. 我还有仍在使用 SQL Server 2012 的客户吗？

DROP TABLE IF EXISTS ##AuditLogTempTable1

Note: This also works for ## temp tables.

i.e.

IF OBJECT_ID('tempdb.dbo.##AuditLogTempTable1', 'U') IS NOT NULL
DROP TABLE ##AuditLogTempTable1

Note: This type of command only suitable post SQL Server 2016.
Ask yourself .. Do I have any customers that are still on SQL Server 2012 ?

DROP TABLE IF EXISTS ##AuditLogTempTable1
回复 原文
世态炎凉 2024-07-22 02:14:39

当我已经创建临时表时，我通常会遇到此错误； 检查 SQL 语句是否有错误的代码会看到“旧”临时表，并在后续语句中返回列数错误计数，就好像临时表从未被删除一样。

在创建具有较少列的版本后更改临时表中的列数后，删除该表，然后运行查询。

I usually hit this error when I have already created the temp table; the code that checks the SQL statement for errors sees the "old" temp table in place and returns a miscount on the number of columns in later statements, as if the temp table was never dropped.

After changing the number of columns in a temp table after already creating a version with less columns, drop the table and THEN run your query.

回复 原文
分分钟 2024-07-22 02:14:39

我最近看到一位 DBA 做了类似的事情：

begin try
    drop table #temp
end try

begin catch 
    print 'table does not exist'
end catch 

create table #temp(a int, b int)

I recently saw a DBA do something similar to this:

begin try
    drop table #temp
end try

begin catch 
    print 'table does not exist'
end catch 

create table #temp(a int, b int)
回复 原文
长安忆 2024-07-22 02:14:39

我的代码使用会更改的 Source 表和必须与这些更改匹配的 Destination 表。

-- 
-- Sample SQL to update only rows in a "Destination" Table
--  based on only rows that have changed in a "Source" table
--


--
-- Drop and Create a Temp Table to use as the "Source" Table
--
IF OBJECT_ID('tempdb..#tSource') IS NOT NULL drop table #tSource
create table #tSource (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Insert some values into the source
--
Insert #tSource (Col1, Col2, Col3, Col4) Values(1,1,1,1)
Insert #tSource (Col1, Col2, Col3, Col4) Values(2,1,1,2)
Insert #tSource (Col1, Col2, Col3, Col4) Values(3,1,1,3)
Insert #tSource (Col1, Col2, Col3, Col4) Values(4,1,1,4)
Insert #tSource (Col1, Col2, Col3, Col4) Values(5,1,1,5)
Insert #tSource (Col1, Col2, Col3, Col4) Values(6,1,1,6)

--
-- Drop and Create a Temp Table to use as the "Destination" Table
--
IF OBJECT_ID('tempdb..#tDest') IS NOT NULL drop Table #tDest
create table #tDest (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Add all Rows from the Source to the Destination
--
Insert #tDest
Select Col1, Col2, Col3, Col4 from #tSource


--
-- Look at both tables to see that they are the same
--
select *
from #tSource
Select *
from #tDest

--
-- Make some changes to the Source
--
update #tSource
    Set Col3=19
    Where Col1=1
update #tSource
    Set Col3=29
    Where Col1=2
update #tSource
    Set Col2=38
    Where Col1=3
update #tSource
    Set Col2=48
    Where Col1=4

--
-- Look at the Differences
-- Note: Only 4 rows are different. 2 Rows have remained the same.
--
Select Col1, Col2, Col3, Col4
from #tSource
except
Select Col1, Col2, Col3, Col4
from #tDest

--
-- Update only the rows that have changed
-- Note: I am using Col1 like an ID column
--
Update #tDest
    Set Col2=S.Col2,
        Col3=S.Col3,
        Col4=S.Col4
From    (   Select Col1, Col2, Col3, Col4
            from #tSource
            except
            Select Col1, Col2, Col3, Col4
            from #tDest
        ) S
Where #tDest.Col1=S.Col1 

--
-- Look at the tables again to see that
--  the destination table has changed to match
--  the source table.

select *
from #tSource
Select *
from #tDest

--
-- Clean Up
--
drop table #tSource
drop table #tDest

My code uses a Source table that changes, and a Destination table that must match those changes.

-- 
-- Sample SQL to update only rows in a "Destination" Table
--  based on only rows that have changed in a "Source" table
--


--
-- Drop and Create a Temp Table to use as the "Source" Table
--
IF OBJECT_ID('tempdb..#tSource') IS NOT NULL drop table #tSource
create table #tSource (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Insert some values into the source
--
Insert #tSource (Col1, Col2, Col3, Col4) Values(1,1,1,1)
Insert #tSource (Col1, Col2, Col3, Col4) Values(2,1,1,2)
Insert #tSource (Col1, Col2, Col3, Col4) Values(3,1,1,3)
Insert #tSource (Col1, Col2, Col3, Col4) Values(4,1,1,4)
Insert #tSource (Col1, Col2, Col3, Col4) Values(5,1,1,5)
Insert #tSource (Col1, Col2, Col3, Col4) Values(6,1,1,6)

--
-- Drop and Create a Temp Table to use as the "Destination" Table
--
IF OBJECT_ID('tempdb..#tDest') IS NOT NULL drop Table #tDest
create table #tDest (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Add all Rows from the Source to the Destination
--
Insert #tDest
Select Col1, Col2, Col3, Col4 from #tSource


--
-- Look at both tables to see that they are the same
--
select *
from #tSource
Select *
from #tDest

--
-- Make some changes to the Source
--
update #tSource
    Set Col3=19
    Where Col1=1
update #tSource
    Set Col3=29
    Where Col1=2
update #tSource
    Set Col2=38
    Where Col1=3
update #tSource
    Set Col2=48
    Where Col1=4

--
-- Look at the Differences
-- Note: Only 4 rows are different. 2 Rows have remained the same.
--
Select Col1, Col2, Col3, Col4
from #tSource
except
Select Col1, Col2, Col3, Col4
from #tDest

--
-- Update only the rows that have changed
-- Note: I am using Col1 like an ID column
--
Update #tDest
    Set Col2=S.Col2,
        Col3=S.Col3,
        Col4=S.Col4
From    (   Select Col1, Col2, Col3, Col4
            from #tSource
            except
            Select Col1, Col2, Col3, Col4
            from #tDest
        ) S
Where #tDest.Col1=S.Col1 

--
-- Look at the tables again to see that
--  the destination table has changed to match
--  the source table.

select *
from #tSource
Select *
from #tDest

--
-- Clean Up
--
drop table #tSource
drop table #tDest
回复 原文
一个人的夜不怕黑 2024-07-22 02:14:39

是的，“无效列”此错误是从“从 #Results 选择公司、stepid、fieldid、NewColumn”行引发的。

运行t-sql有两个阶段，

首先是解析，在此阶段sql服务器检查您提交的sql字符串的正确性，包括表的列，并优化您的查询以获得最快的检索。

第二，运行，检索数据。

如果表 #Results 存在，则解析过程将检查您指定的列是否有效，否则（表不存在）解析将绕过您指定的检查列。

Yes, "invalid column" this error raised from the line "select company, stepid, fieldid, NewColumn from #Results".

There are two phases of runing t-sql,

first, parsing, in this phase the sql server check the correction of you submited sql string, including column of table, and optimized your query for fastest retreival.

second, running, retreiving the datas.

If table #Results exists then parsing process will check the columns you specified are valid or not, else (table doesn't exist) parsing will be by passsed the checking columns as you specified.

回复 原文
喜爱纠缠 2024-07-22 02:14:39

从 SQL Server 2016 开始，您可以使用

DROP TABLE IF EXISTS #TempTable

旧版本的 sql，

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    DROP TABLE #TempTable

而不是删除表，您可以再次使用它

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    TRUNCATE TABLE #TempTable
ELSE
    CREATE TABLE #TempTable (ID int,COL1 varchar(20))

Since SQL Server 2016 you can use

DROP TABLE IF EXISTS #TempTable

and older versions of sql you can use

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    DROP TABLE #TempTable

instead of deleting the table you can trancate and use it again

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    TRUNCATE TABLE #TempTable
ELSE
    CREATE TABLE #TempTable (ID int,COL1 varchar(20))
回复 原文
只怪假的太真实 2024-07-22 02:14:39

当您更改临时表中的列时，必须先删除该表，然后才能再次运行查询。 （是的，这很烦人。正是你必须做的。）

我一直认为这是因为“无效列”检查是由解析器在运行查询之前完成的，因此它基于之前表中的列被删除了……pnbs 也是这么说的。

When you change a column in a temp table, you must drop the table before running the query again. (Yes, it is annoying. Just what you have to do.)

I have always assumed this is because the "invalid column" check is done by parser before the query is run, so it is based on the columns in the table before it is dropped..... and that is what pnbs also said.

回复 原文
~没有更多了~

关于作者

萌能量女王

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

诺曦

文章 0 评论 0

要走干脆点

文章 0 评论 0

把回忆走一遍

文章 0 评论 0

陌上青苔

文章 0 评论 0

Arthur

文章 0 评论 0

哄哄

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文