在存储过程中删除并重复使用临时表

Question

我需要使用循环多次SELECT INTO创建一个临时表，但我无法这样做，因为在使用SELECT INTO创建的表结束循环后不能简单地删除该表，因为在同一批处理中无法删除并重新创建表。那么，在存储过程中如何删除表并重新创建它？是否可以在不使用临时表的情况下完成此操作？以下是我实际使用临时表的代码片段，其目的是进行透视算法：

WHILE @offset<@NumDays BEGIN
    SELECT 
    bg.*, j.ID, j.time, j.Status
    INTO #TEMP1
    FROM #TEMP2 AS bg
    left outer join PersonSchedule j on bg.PersonID = j.PersonID and
    j.TimeSlotDateTime = @StartDate + @offset

    DROP TABLE #TEMP2;
    SELECT * INTO #TEMP2 FROM #TEMP1 
    DROP TABLE #TEMP1

    SET @offset = @offset + 1
END

- fire in the hole

---

由于每次迭代表结构都会发生变化。 - Stanislav Kniazev

希望公共表达式（CTEs）能够满足您的需求。如果不行，我可以想到一种相当可怕的方法，涉及动态SQL和以NEWID()命名的全局临时表。 - Martin Smith

在第一次循环迭代之前，#TEMP2 是如何定义的？ - binki

Answer 1

在SQL Server 2008中，您可以在存储过程中的循环中删除和重新创建表：

create procedure CreateTablesInALoop
as
declare @i int
set @i = 0
while @i < 100
    begin
    select 1 as id into #t
    drop table #t
    set @i = @i + 1
    print 'Yay'
    end
go
exec CreateTablesInALoop
go

- Andomar

---

谢谢，但我目前正在使用SQL Server 2005 :(。 - fire in the hole

奇怪的是它也可以在2005上执行。 - fire in the hole

Answer 2

你需要对临时表做什么？

一个选择是使用表变量，而不是临时表。

或者你可以尝试像这样使用公共表达式：

  WHILE @offset<@NumDays  
   BEGIN
        WITH tmp1 AS (SELECT 
        bg.*, j.ID, j.time, j.Status
        FROM #TEMP2 AS bg
        left outer join PersonSchedule j on bg.PersonID = j.PersonID and
        bg.TimeSlotDateTime = j.TimeSlotDateTime and
        j.TimeSlotDateTime = @StartDate + @offset
    )

SELECT * FROM tmp1 

    SET @offset = @offset + 1
END

- Thea

---

我不得不使用临时表，因为它们可以通过SELECT INTO自动创建正确的模式，因为该表没有固定的模式，所以我不能使用表变量。（在每次循环迭代中，新列都会添加到临时表中） 公共表达式可能是解决方案，我会尝试一下;)。 - fire in the hole

Answer 3

我知道这是一篇较旧的帖子，但我想指出原始作者的脚本为何会出错，而提供的示例答案却没有。在原帖中，尽管他执行了DROP TABLE，但他还执行了2个单独的SELECT INTO命令。而建议的解决方案只执行了1个SELECT INTO（在循环中）。原帖的过程在编译时失败了。编译器看到了两次创建相同表的尝试，而在建议的解决方案中，编译器只看到了1次创建表的尝试，因此允许创建该过程。

- SPO100