asked
using a temp table in a while loop
Hi all ,I want to use a temp table in a while loop with a number of union all, but for the 2nd iteration it says that the temp table already exists!!!
Your question is a little light on detail, so I'm going to guess a little here. I'm assuming you have a loop in which you are creating and populating a temp table. while @i < 4 begin create table #mytemp --do something end The issue you have is that the temporary table (assuming you are using a local temp table) remains until the connection is broken (per [this msdn page][1]). Think of it as there being an implicit drop table statement at the end of your code, only executed when you close the connection (or at the end of a SP). To resolve this, you either need to drop the temp table within the loop, or create and destroy it outside the loop only. while @i < 4 begin create table #mytemp --do something drop table #mytemp end or create table #mytemp while @i < 4 begin --do something end drop table #mytemp However, there may well be a better way of accomplishing what you are trying to - can you let us know what it is that you are trying to achieve, so we better help you? [1]:
http://msdn.microsoft.com/en-gb/library/ms186986(v=sql.105).aspx
Thank you for posting your code. You are using the syntax Select..Into #Salary. Whilst this will work outside of a loop, you can think of this as create table and select statements combined in one. This means effectively you are doing as I suspected (creating the table inside the where clause). I think your solution will be to use an explicit CREATE TABLE statement before your while loop, then use an insert...select statement inside the loop. That will put all of your selections into the #Salary table (which I assume is your goal?)
TNX FOR YOUR HELP. MY CASE IS AS BELOW: use malib declare @CurrentYear char(4),@CurrentMonth char(2),@SelectedPersons varchar(100) declare @i int set @CurrentYear='1391' set @CurrentMonth='01' set @SelectedPersons='0' set @i = 1 while @i < 13 SELECT 1 as Type, a number of fields Into #Salary FROM PAYchSalaryInf INNER JOIN PAYchPersInf ON an exp WHERE some conditions UNION ALL SELECT 1 as Type, a number of fields FROM PAYchSalaryInf INNER JOIN PAYchPersInf ON an exp WHERE some conditions …. Set @i = @i + 1 RUNNING THIS SP , THE ANSWER IS : (77151 row(s) affected) Msg 2714, Level 16, State 6, Line 9 There is already an object named '#Salary' in the database. I THINK I SHOULD CHANGE THE INSERT INTO STATEMENT. I APPRECIATE YOUR KINDNESS.
Rather create the #temp table first before the LOOP and then do the INSERT: use malib declare @CurrentYear char(4),@CurrentMonth char(2),@SelectedPersons varchar(100) declare @i int set @CurrentYear='1391' set @CurrentMonth='01' set @SelectedPersons='0' set @i = 1 create table #Salary ([Type] int null ,NumberOfFields varchar(20) null ) while @i < 13 INSERT #Salary SELECT 1 as Type, a number of fields FROM PAYchSalaryInf INNER JOIN PAYchPersInf ON an exp WHERE some conditions UNION ALL SELECT 1 as Type, a number of fields FROM PAYchSalaryInf INNER JOIN PAYchPersInf ON an exp WHERE some conditions …. Set @i = @i + 1
15 People are following this question.
