SQL 数据库中已经有一个名为“#columntable”的对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4245444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
There is already an object named '#columntable' in the database
提问by Mohammad Nadeem
I am trying the following query
我正在尝试以下查询
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
But I am getting the error
但我收到错误
Msg 2714, Level 16, State 1, Line 8
There is already an object named '#columntable' in the database.
Can anyone suggest why? The same query works fine if I do not write the else part.
谁能建议为什么？如果我不写 else 部分，同样的查询工作正常。
采纳答案by Tony
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
临时表不会在查询结束时自动删除，只有在删除与数据库的当前连接或您使用以下命令显式删除它们时 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)
在查询开始时测试表是否存在，或者总是在最后删除它（最好是两者）
EDIT: 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.
编辑：正如 Matrin 在他的评论中所说，这实际上是一个解析错误。如果仅在执行 SQL 时解析 SQL，则会出现相同的错误。
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).
解析器对此很满意。有趣的是，如果您改为使用非临时表，原始查询可以很好地解析（我意识到会产生问题，我只是想找出查询无法解析的原因）。
回答by SharpC
This is a SQL Server parser error unfortunately (confirmed by Microsoft).
不幸的是，这是一个 SQL Server 解析器错误（由 Microsoft 确认）。
@DizGrizz is also right - SELECT .. INTO #SomeTabledoesn't work if repeated in IF .. ELSEstatements.
@DizGrizz 也是对的 -SELECT .. INTO #SomeTable如果在IF .. ELSE语句中重复则不起作用。
IF .. ELSE .. CREATE TABLE #SomeTempTable
如果 .. ELSE .. 创建表 #SomeTempTable
In answer to the actual question, creating then altering the table works (you also only have to check and drop once)...
在回答实际问题时，创建然后更改表有效（您也只需检查和删除一次）...
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END
CREATE TABLE #MyTempTable (DummyColumn BIT)
IF EXISTS (SELECT 1 FROM EMP WHERE EID = 6)
BEGIN
ALTER TABLE #MyTempTable
ADD MyColumnType1 VARCHAR(100)
ALTER TABLE #MyTempTable
DROP COLUMN DummyColumn
END
ELSE
BEGIN
ALTER TABLE #MyTempTable
ADD MyColumnType2 VARCHAR(100)
ALTER TABLE #MyTempTable
DROP COLUMN DummyColumn
END
IF .. ELSE .. SELECT INTO #SomeTempTable
IF .. ELSE .. SELECT INTO #SomeTempTable
The issue I had however was the same as @DizGrizz: IF .. ELSEcombined with SELECT .. INTO #SomeTablefails. As a workaround it's possible to select the top 0 rows (i.e. none) to create the table with the correct column types. (This insulates the script from column type changes and also avoids the pain of declaring every type.) INSERT INTOcan then be used, provided IDENTITY_INSERTis set to ONto prevent errors:
然而，我遇到的问题与@DizGrizz 相同：IF .. ELSE结合SELECT .. INTO #SomeTable失败。作为一种解决方法，可以选择前 0 行（即无）来创建具有正确列类型的表。（这将脚本与列类型更改隔离开来，也避免了声明每种类型的痛苦。） INSERT INTO然后可以使用，前提IDENTITY_INSERT是设置ON为防止错误：
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable
-- This creates the table, but avoids having to declare any column types or sizes
SELECT TOP 0 KeyNm
INTO #MyTempTable
FROM dbo.MyDataTable2
-- Required to prevent IDENTITY_INSERT error
SET IDENTITY_INSERT #MyTempTable ON
IF @something = 1
BEGIN
-- Insert the actual rows required into the (currently empty) temp table
INSERT INTO #MyTempTable (KeyNm)
SELECT KeyNm
FROM dbo.MyDataTable2
WHERE CatNum = 2
END
ELSE
BEGIN
-- Insert the actual rows required into the temp table
INSERT INTO #MyTempTable (KeyNm)
SELECT KeyNm
FROM dbo.MyDataTable2
WHERE CatNum = 8
END
SET IDENTITY_INSERT #MyTempTable OFF
回答by DizGrizz
This also occurs if you create the tables with SELECT INTO...as in
如果您使用 SELECT INTO... 创建表，也会发生这种情况
IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
DROP TABLE #MyTempTable
SELECT TOP 1 @MyVariable = ScaleValue
FROM MyDataTable1
WHERE ProductWeight > 1000
IF @MyVariable = 1
BEGIN
SELECT KeyNm
INTO #MyTempTable
FROM dbo.MyDataTable2
WHERE CatNum = 2
END
ELSE
BEGIN
SELECT KeyNm
INTO #MyTempTable
FROM dbo.MyDataTable2
WHERE CatNum = 8
END
The parser should not even attempt to detect this because, in many cases, it would be impossible for the parser to determine if the table would already exist. The code above is a perfect example...there would be no way for the parser to determine the value of @MyVariable. I hope that someone has informed MS of this bug (I don't have their ear).
解析器甚至不应该尝试检测这一点，因为在许多情况下，解析器不可能确定该表是否已经存在。上面的代码是一个完美的例子……解析器无法确定@MyVariable 的值。我希望有人已经将这个错误告知 MS（我没有听他们的意见）。
回答by Eric Hartford
Use global temp tables and wrap the select in exec.
使用全局临时表并将选择包装在 exec 中。
Example:
例子：
Fail
失败
declare @a int = 1
if object_id('tempdb..##temp') is not null drop table ##temp
if(@a = 1) select * into ##temp from table_1
else if(@a = 2) select * into ##temp from table_2
Win
赢
declare @a int = 1
if object_id('tempdb..##temp') is not null drop table ##temp
if(@a = 1) exec('select * into ##temp from table_1')
else if(@a = 2) exec('select * into ##temp from table_2')
This will fool the buggy parser that is trying to be smarter than it is. And Microsoft - please fix this.
这将愚弄试图变得更聪明的错误解析器。和微软 - 请解决这个问题。
回答by Jens
You can check if it exists by doing:
您可以通过执行以下操作来检查它是否存在：
IF OBJECT_ID('tempdb..#columntable') IS NOT NULL
BEGIN
DROP TABLE #columntable
PRINT 'Dropped table...'
END
回答by Mohammad Nadeem
Well I got the answer... As Martin said this is a parse/compile issue. So I Tried changing my script as below
好吧，我得到了答案……正如 Martin 所说，这是一个解析/编译问题。所以我尝试改变我的脚本如下
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
go
if exists (select 1 from emp where eid = 1)
begin
if object_id('tempdb..#columntable') is not null
begin
drop table #columntable
end
create table #columntable (newcolumns varchar(100))
end
And this worked for me.
这对我有用。
回答by Joel Mansford
The error is wrong, remove the if clause and it runs through fine. Thus the problem is in the exists:
错误是错误的，删除 if 子句，它运行良好。因此问题在于存在：
if object_id('tempdb..#columntable') is not null
begin
drop table #columntable
end
create table #columntable (oldcolumns varchar(100))
