deleting and reusing a temp table in a stored precedure

I need to SELECT INTO a temp table multiple times with a loop but I just can't do it, because after the table created by SELECT INTO you can't simply drop the table at the end of the loop, because you can't delete a table and create it again in the same batch.

so how can I delete a table in a stored procedure and create it again? is it possible to this without using a temp table?

here is a snippet of where I am actually using the temp table which is supposed to be a pivoting algorithm:

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

In SQL Server 2008, you are allowed to drop and recreate tables in a loop in a stored procedure:

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

What you need to do with the temp table?

One option is to use table variable, not temporary table.

Or you can try using Common Table Expressions like this:

  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

I know this is an older post, but I wanted to point out why the original posters script threw an error, while the sample answer provided did not.

In the OP's example although he did a DROP TABLE, he also did 2 separate SELECT into commands. While the suggested solution did only 1 SELECT INTO (in a loop). The OP's procedure failed on Compilation. The compiler saw two attempts to create the same table, while in the suggested solution the compiler only saw 1 attempt to create the table therefore it allowed the procedure to be created.