不能在同一批次中两次创建相同的 #temp 表 [英] Cannot create the same #temp table twice in the same batch

问题描述

在SQLServer中写了一个很简单的测试用例，不太明白为什么不行:

Wrote a very simple test case in SQLServer and don't quite understand why it doesn't work:

create table #temp(
  id int,
  val int)

insert into #temp values (1, 1), (2, 2)
select * from #temp

if object_id('tempdb..#temp') is not null
   drop table #temp

create table #temp(
  id int,
  val int)

insert into #temp values (1, 1), (2, 2)
select * from #temp

推荐答案

请参阅 文档，其中指出:

如果在单个存储过程或批处理中创建了多个临时表，它们必须具有不同的名称.

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

您的代码产生以下错误:

Your code yields the following error:

消息 2714，级别 16，状态 1，第 11 行
数据库中已经有一个名为#temp"的对象.

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

而这并不是因为表已被删除且无法重新创建；这段代码永远不会被执行，解析器实际上看到你试图创建同一个表两次(而且它无法看到像你的 DROP 命令那样的逻辑).

And this is not because the table has been dropped and can't be re-created; this code never gets executed, the parser actually sees you trying to create the same table twice (and it has no ability to see logic like your DROP command).

除了使用两个不同的 #temp 表名称之外，另一种解决方法是只创建一次表，并在完成第一段代码后将其截断.

Other than using two different #temp table names, another workaround would be to just create the table once, and truncate it when you're done with your first bit of code.

这篇关于不能在同一批次中两次创建相同的 #temp 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！