数据库中已经有一个名为“#columntable"的对象 [英] There is already an object named '#columntable' in the database

问题描述

我正在尝试以下查询

   if exists (select 1  from emp where eid = 6)     
     begin
        if object_id('tempdb..#columntable') is not null 
          begin 
             drop table #columntable                         
          end                     
        create table #columntable (oldcolumns varchar(100))   
     end
  else
     begin
        if object_id('tempdb..#columntable') is not null 
          begin 
             drop table #columntable            
          end     


    create table #columntable (newcolumns varchar(100))   
 end

但我收到错误

Msg 2714, Level 16, State 1, Line 8
There is already an object named '#columntable' in the database.

谁能建议为什么?如果我不写 else 部分，同样的查询也能正常工作.

Can anyone suggest why? The same query works fine if I do not write the else part.

推荐答案

临时表不会在查询结束时自动删除，仅当当前与数据库的连接被删除或您使用 DROP 显式删除它们时表#columntable

Temp tables are not dropped automatically at the end of a query, only when the current connection to the DB is dropped or you explicitly delete them with DROP TABLE #columntable

要么在查询开始时测试该表是否存在，要么总是在最后删除它(最好两者兼而有之)

Either test for the existence of the table at the start of the query or alwayas delete it at the end (preferably both)

正如 Matrin 在他的评论中所说，这实际上是一个解析错误.如果只在执行时解析 SQL，则会出现相同的错误.

As Matrin said in his comment, this is actually a parse error. You get the same error if you only parse the SQL as when you execute it.

为了测试这一点，我拆分了您的查询并尝试了:

To test that out I split up your query and tried:

if exists (select 1 from emp where id = 6)
  create table #columntable (newcolumns varchar(100))
GO
if not exists (select 1 from emp where id = 6)
  create table #columntable (oldcolumns varchar(100))
GO

解析器对此很满意.有趣的是，如果您改为使用非临时表，原始查询解析得很好(我意识到会产生问题，我只是想找出查询无法解析的原因).

The parser is happy with that. Interestingly if you change to using non-temp tables the original query parses fine (I realise the problems that would create, I was just interested to find out why the query would not parse).

这篇关于数据库中已经有一个名为“#columntable"的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！