deleting and reusing a temp table in a stored precedures
- by Sheagorath
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
