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( in 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?

here is a snippet of where I am actualy 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
    bg.TimeSlotDateTime = j.TimeSlotDateTime and
    j.TimeSlotDateTime = @StartDate + @offset

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

    SET @offset = @offset + 1
END