删除临时表后我可以重新创建它吗? [英] Can I recreate a temp table after dropping it?

问题描述

鉴于:

存储过程中的代码:

select bleh
  into #tblTemp
  from FunctionThatReturnsTable('some','params')

-- do some stuff

drop table #tblTemp

-- Error on this command:
-- 'There is already an object named '#tblTemp' in the database.'
select bleh
  into #tblTemp
  from FunctionThatReturnsTable('some','other params')

问题:

我无法重新创建此临时表.我的解决方法是使用#tmpTable1、#tmpTable2、#tempTable3 等.有没有办法解决这个问题?每次只使用一个临时表就好了.

I can't recreate this temp table. My work around is to use #tmpTable1, #tmpTable2, #tempTable3 etc. Is there a way I can get around this? It would be nice just use one temp table each time.

如果不是，这是什么原因?

If not, what is the reason for this?

推荐答案

正如我的评论所反映的那样，我将建议答案是为您创建的每个对象使用不同的 #temp 表名称.这有点像对医生说，当我这样做时会很痛."他可能的反应是，别再那样做了！"

As my comment reflected, I'm going to suggest that the answer is that you use a different #temp table name for each object that you create. It's kind of like saying to the doctor, "it hurts when I do this." His likely response is going to be, "stop doing that!"

这是一个问题的原因是 SQL Server 的解析器试图一次性解析整个批处理.它可以清楚地看到您多次尝试创建相同的 #temp 表，但忽略了中间的 DROP 命令(我无法确切地告诉您为什么会这样，因为我没有访问源代码).这与您不能这样做的原因相同:

The reason this is a problem is that SQL Server's parser attempts to parse the entire batch in one shot. It can clearly see that you are trying to create the same #temp table multiple times, but ignores the DROP command in between (I can't tell you exactly why that is, as I don't have access to the source code). This is the same reason you can't do this:

IF (1=1)
  CREATE TABLE #foo(i INT);
ELSE
  CREATE TABLE #foo(i VARCHAR(32));

解析器看到两个相同的名字，但不能真正遵循IF/ELSE逻辑.

The parser sees the two identical names, but can't really follow the IF/ELSE logic.

除了避免多个同名#temp 表导致解析器出现问题之外，使用唯一名称的另一个好处是，如果您不明确删除它们，它们可以被重新使用.这将减轻 tempdb 在元数据/锁定方面的负载.

In addition to avoiding the problems multiple identically-named #temp tables causes the parser, another benefit to using unique names is that they can be re-used if you don't explicitly drop them. This will lighten the load on tempdb in terms of metadata / locking.

这篇关于删除临时表后我可以重新创建它吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！