Delete Temp Table within a Cursor

Question

Post reply

Delete Temp Table within a Cursor

AVB

SSChampion

Points: 11418
More actions
October 13, 2007 at 7:07 am

#62619

I have a cursor that is inserting data into a temp table. I am using an ID seed column on the temp table called Rank. This is so I know where paticular data is in the row set.
The insert for the temp table is being called within the cursor and I have a delete Temp Table Statement right before the insert. When I run this cursor and return each record set for the temp table the ID seed keeps incrementing. I've tried putting the delete temp table statement before opening the cursor and I've also put it right after the Fetch Statment way before the insert statement.
The delete statment is successfully being called each time. Is this just a caveat of temp tables within cursors or am I doing something wrong?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AVB SSChampion Points: 11418 More actions · Answer 1

Okay I fixed it... I needed to issue a DROP TABLE command before I called another FETCH NEXT statment from the Cursor.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 2

You could have also used TRUNCATE table which resets the ID column back to 1.

But, even that doesn't "Fix" it... why are you using a cursor? Perhaps if you post the code, we might offer a high speed suggestion... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 3

Jeff,

Thanks for the post. I wasn't aware of the Truncate option. I'll try that too although my DROP TABLE worked as well. As far as the cursor I would love to see an example of how I could avoid it.

The Cursor I use isn't anything special just a normal cursor with the exception of the temp table create and insert.

I Declare my cursor, open it and while @@FetchStatus = 0 I BEGIN Creating the #Table then Insert with the result set of a Stored Proc which is executed with the value from the cursor.

Then I select data from the Temp Table and insert it into a permanent table drop the #Table then fetch the next record and do it all over again. 🙂 The code is posted below:

--============== Rate Cursor ================

DECLARE @ORIG varchar(6),@DEST VARCHAR(6),@Weight int,@Car_ID int,@B_ID int,

@Rank tinyint

SET NOCOUNT ON

DECLARE SRateCursor CURSOR FOR

select @Orig,@Dest,@Weight,@Car_ID,@B_ID from t_table a

OPEN SRateCursor

FETCH NEXT FROM SRateCursor INTO @Orig,@Dest,@Weight,@Car_ID,@B_ID

WHILE @@FETCH_STATUS = 0

BEGIN

--========= Create Temp Table ===============

IF Object_ID ('tempdb.dbo.#SRateTable') is null

BEGIN

Create Table

#SRateTable ([Rank] [int] IDENTITY(1,1) NOT NULL,Car_ID int,Rate int)

END

--====== Clear Temp Table Data then Insert new data =========

Delete dbo.#SRateTable

Insert #RateTable (Rank,ID,Car_ID,Rate)

exec dbo.s_CRateList @Orig,@Dest,@Weight

--====== Determine Rank =============

select @Rank = RANK from #SRateTable where Car_ID = @Car_Id

INSERT INTO t_SRateTable (B_ID,Car_ID,Rank)

VALUES (@B_ID,@Car_ID,@Rank)

DROP TABLE #SRateTable

FETCH NEXT

FROM SRateCursor INTO @Orig,@Dest,@Weight,@Car_ID,@B_ID

END

CLOSE SRateCursor

DEALLOCATE SRateCursor

Lowell SSC Guru Points: 323518 More actions · Answer 4

it looks to me like you are inserting

data into t_SRateTable, but doing it in a RBAR(Row-By-Agonizing-Row Basis) (--Jeff Moden 2005-2007)

It seems like you could just insert the whole batch.

the procedure s_FRCBL_CarrierRateList seems to return the B_ID for a unique @Orig,@Dest,@Weight combination,

so i think if we saw that procs code, it could be done in a single SQL statement.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 5

I agree... it seems like the called proc is for a GUI. If it's not too complex (or even if it is), you might want to consider rewritting it to handle batches...

If you decide to keep the cursor, you might want to try it in this general order... this way keeps from recreating the temp table for every loop and doesn't have the overhead of deletes...

Create the temp table

Form the cursor

Start of cursor loop

Truncate the temp table

...do the other stuff within the cursor loop...

End of cursor loop

Deallocate the cursor

No need to explicitly drop the temp table... session should drop it when it ends (although some folks do like to do "housekeeping" at the end of each proc).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AVB SSChampion Points: 11418 More actions · Answer 6

Lowell,

The proc produces a record set of 5-6 rows on average. What I am doing is comparing data from the cursor (The Car_ID) and finding out which row it is in after runing the proc...thus giving me the RANK (selet RANK from #Table where Car_ID = @Car_ID. I am comparing the amount of customers that picked the 1st price (the lowest) then the 2nd, 3rd price etc... The insert was so I could run a count statment,export or do a join with the rest of the tables if I needed to compare more data.

Jeff Gray SSChampion Points: 10667 More actions · Answer 7

Another good trick for cursors:

Declare cursor foo....

Declare @someVariable INT

open foo

While (1=1)

BEGIN

Fetch foo into @someVariable

IF @@Fetch_Status <> 0 BREAK -- no more data

--do something here

END

This eliminates the need for two separate fetch statements.