Cursor stopped working after working for a while. Now only inserts first record into the table

Question

Post reply

Cursor stopped working after working for a while. Now only inserts first record into the table

vaveryanov

Newbie

Points: 5
More actions
July 11, 2012 at 1:22 pm

#258824

DECLARE cur_ProdHistMstr CURSOR FORWARD_ONLY FOR
SELECT convert(datetime,absdate) as absDate
, 'Pack'
, Item
, ItemQty
, 'G'
, 'QAD'
, getdate()
, right(contID,2)
, siteid
, recordID
FROM #ProdHistMaster
Order by absDate
OPEN cur_ProdHistMstr
FETCH NEXT FROM cur_ProdHistMstr INTO @PRODDATE, @PROCESSID, @PARTID,@QUANTITY,@GRS,@UPDATEDBY,
@UPDATEDTIME, @INSERIAL, @COMPANY, @QADRecID
UPDATE PROCINDEXCOUNTER SET PROCINDEX=PROCINDEX+1
SELECT @PROCINDEX = PROCINDEXCOUNTER.PROCINDEX - 1 FROM PROCINDEXCOUNTER
INSERT INTO dbo.ProdHistMaster
( PRODDATE
,PROCESSID
,PARTID
,PROCINDEX
,QUANTITY
,GRS
,UPDATEDBY
,UPDATEDTIME
,INSERIAL
,COMPANY
,QADRecID
)
SELECT
@PRODDATE
, @PROCESSID
, @PARTID
, @PROCINDEX
, left(@QUANTITY,3)
, @GRS
, @UPDATEDBY
, @UPDATEDTIME
, @INSERIAL
, @COMPANY
, @QADRecID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cur_ProdHistMstr INTO @PRODDATE, @PROCESSID, @PARTID,@QUANTITY,@GRS,@UPDATEDBY,
@UPDATEDTIME, @INSERIAL, @COMPANY, @QADRecID
UPDATE PROCINDEXCOUNTER SET PROCINDEX=PROCINDEX+1
SELECT @PROCINDEX = PROCINDEXCOUNTER.PROCINDEX - 1 FROM PROCINDEXCOUNTER
END
CLOSE cur_ProdHistMstr
DEALLOCATE cur_ProdHistMstr

Viewing 10 posts - 1 through 10 (of 10 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I don't see an INSERT inside the while loop.

May be blind. 😎

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Your insert is outside of the cursor as Lynn pointed out but the bigger question is why do you need a cursor at all? This is a single insert and a single update statement. I see no reason at all for a cursor.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Hard to tell for sure without ddl, sample data and desired output but I think this is pretty much the same thing...

Something like this perhaps?

INSERT INTO dbo.ProdHistMaster

( PRODDATE

,PROCESSID

,PARTID

,PROCINDEX

,QUANTITY

,GRS

,UPDATEDBY

,UPDATEDTIME

,INSERIAL

,COMPANY

,QADRecID

)

SELECT convert(datetime,absdate) as absDate

, 'Pack'

, Item

, ItemQty

, 'G'

, 'QAD'

, getdate()

, right(contID,2)

, siteid

, recordID

FROM #ProdHistMaster

Order by absDate

UPDATE PROCINDEXCOUNTER SET PROCINDEX = PROCINDEX + @@ROWCOUNT

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Sean Lange (7/11/2012)
Hard to tell for sure without ddl, sample data and desired output but I think this is pretty much the same thing...
Something like this perhaps?
INSERT INTO dbo.ProdHistMaster
( PRODDATE
,PROCESSID
,PARTID
,PROCINDEX
,QUANTITY
,GRS
,UPDATEDBY
,UPDATEDTIME
,INSERIAL
,COMPANY
,QADRecID
)
SELECT convert(datetime,absdate) as absDate
, 'Pack'
, Item
, ItemQty
, 'G'
, 'QAD'
, getdate()
, right(contID,2)
, siteid
, recordID
FROM #ProdHistMaster
Order by absDate
UPDATE PROCINDEXCOUNTER SET PROCINDEX = PROCINDEX + @@ROWCOUNT

You don't have enough columns in the select list to match the insert columns. Your short one.

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Lynn Pettis (7/11/2012)
You don't have enough columns in the select list to match the insert columns. Your short one.

Further proof we need ddl and sample data. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323547 More actions · Answer 6

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!

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 7

SOME DLL would be good hover i have re arrangged your cursor to do what i think you mean.

DECLARE cur_ProdHistMstr CURSOR FORWARD_ONLY

FOR

SELECT convert(datetime,absdate) as absDate

, 'Pack'

, Item

, ItemQty

, 'G'

, 'QAD'

, getdate()

, right(contID,2)

, siteid

, recordID

FROM #ProdHistMaster

Order by absDate

OPEN cur_ProdHistMstr

FETCH NEXT FROM cur_ProdHistMstr INTO @PRODDATE, @PROCESSID, @PARTID,@QUANTITY,@GRS,@UPDATEDBY,

@UPDATEDTIME, @INSERIAL, @COMPANY, @QADRecID

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE PROCINDEXCOUNTER SET PROCINDEX=PROCINDEX+1

SELECT @PROCINDEX = PROCINDEXCOUNTER.PROCINDEX - 1 FROM PROCINDEXCOUNTER

INSERT INTO dbo.ProdHistMaster

( PRODDATE

,PROCESSID

,PARTID

,PROCINDEX

,QUANTITY

,GRS

,UPDATEDBY

,UPDATEDTIME

,INSERIAL

,COMPANY

,QADRecID

)

SELECT

@PRODDATE

, @PROCESSID

, @PARTID

, @PROCINDEX

, left(@QUANTITY,3)

, @GRS

, @UPDATEDBY

, @UPDATEDTIME

, @INSERIAL

, @COMPANY

, @QADRecID

FETCH NEXT FROM cur_ProdHistMstr INTO @PRODDATE, @PROCESSID, @PARTID,@QUANTITY,@GRS,@UPDATEDBY,

@UPDATEDTIME, @INSERIAL, @COMPANY, @QADRecID

END

CLOSE cur_ProdHistMstr

DEALLOCATE cur_ProdHistMstr

***The first step is always the hardest *******

Sean Lange SSC Guru Points: 286609 More actions · Answer 8

There still is no need for a cursor here at all. A simple insert using Row_Number() should be totally fine.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

New shot in the dark....

declare @PROCINDEX int

select @PROCINDEX = PROCINDEX FROM PROCINDEXCOUNTER

INSERT INTO dbo.ProdHistMaster

( PRODDATE

,PROCESSID

,PARTID

,PROCINDEX

,QUANTITY

,GRS

,UPDATEDBY

,UPDATEDTIME

,INSERIAL

,COMPANY

,QADRecID

)

SELECT

convert(datetime,absdate) as absDate

, 'Pack'

, Item

, ROW_NUMBER() over (order by absDate) + @PROCINDEX

, ItemQty

, 'G'

, 'QAD'

, getdate()

, right(contID,2)

, siteid

, recordID

FROM #ProdHistMaster

Order by absDate

UPDATE PROCINDEXCOUNTER SET PROCINDEX = PROCINDEX + @@ROWCOUNT

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/