deleting and reusing a temp table in a stored precedure

Question

Hi

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

Answer 1

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

Answer 2

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