Cursor Loop Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor Loop Problem

Author

Topic

hifni
Starting Member

4 Posts

Posted - 2004-09-10 : 01:53:02

Hi,
uses: microsoft sql server 2000;

I'm using the Cursor to go through a table and insert the rows of a column to a another table. The Problem I'm facing is that in the end of message pane of the SQL Query Analyser I get a duplicate value of the last row. Below is the sql code I've wrote:

/*DECLARING A VARIABLE TO HOLD THE DEPARTMENT ID */
DECLARE @InDepID Varchar(3)

/*DECLARING A CURSOR */
DECLARE InsertDepCursor CURSOR
LOCAL SCROLL STATIC[code]
FOR
SELECT DepID FROM Departments
ORDER BY DepID

/*OPENS THE CURSOR*/
OPEN InsertDepCursor

/*FETCHES THE FIRST RECORD*/
FETCH NEXT FROM InsertDepCursor INTO @InDepID

/*INSERTS INTO THE PROCESSDEPSUMMARY TABLE WITH TODATE*/
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)

/*PRINTS THE RESULT*/
PRINT '07/31/2004' + ' ' + @InDepID

/*WHILE THE CURSOR COME END OF THE RECORDSET DO*/
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM InsertDepCursor INTO @InDepID
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)
PRINT '07/31/2004' + ' ' + @InDepID

END

CLOSE InsertDepCursor
DEALLOCATE InsertDepCursor

This is the result Message Set i got:
(1 row(s) affected)

07/31/2004 AC

(1 row(s) affected)

07/31/2004 BU

(1 row(s) affected)

07/31/2004 CD

(1 row(s) affected)

07/31/2004 CH

(1 row(s) affected)

07/31/2004 CM

(1 row(s) affected)

07/31/2004 CP

(1 row(s) affected)

07/31/2004 CR

(1 row(s) affected)

07/31/2004 CT

(1 row(s) affected)

07/31/2004 DC

(1 row(s) affected)

07/31/2004 EL

(1 row(s) affected)

07/31/2004 EMU

(1 row(s) affected)

07/31/2004 EN

(1 row(s) affected)

07/31/2004 EX

(1 row(s) affected)

07/31/2004 FI

(1 row(s) affected)

07/31/2004 FIR

(1 row(s) affected)

07/31/2004 FM

(1 row(s) affected)

07/31/2004 GM

(1 row(s) affected)

07/31/2004 GT

(1 row(s) affected)

07/31/2004 HRP

(1 row(s) affected)

07/31/2004 IM

(1 row(s) affected)

07/31/2004 IN

(1 row(s) affected)

07/31/2004 IT

(1 row(s) affected)

07/31/2004 JW

(1 row(s) affected)

07/31/2004 KG

(1 row(s) affected)

07/31/2004 LE

(1 row(s) affected)

07/31/2004 LG

(1 row(s) affected)

07/31/2004 LH

(1 row(s) affected)

07/31/2004 LR

(1 row(s) affected)

07/31/2004 LS

(1 row(s) affected)

07/31/2004 MF

(1 row(s) affected)

07/31/2004 MX

(1 row(s) affected)

07/31/2004 PE

(1 row(s) affected)

07/31/2004 PO

(1 row(s) affected)

07/31/2004 PU

(1 row(s) affected)

07/31/2004 RA

(1 row(s) affected)

07/31/2004 RBF

(1 row(s) affected)

07/31/2004 RG

(1 row(s) affected)

07/31/2004 RSP

(1 row(s) affected)

07/31/2004 SB

(1 row(s) affected)

07/31/2004 SE

(1 row(s) affected)

07/31/2004 SP

(1 row(s) affected)

07/31/2004 SR

(1 row(s) affected)

07/31/2004 TB

(1 row(s) affected)

07/31/2004 TC

(1 row(s) affected)

07/31/2004 TE

(1 row(s) affected)

07/31/2004 TM

(1 row(s) affected)

07/31/2004 TX

(1 row(s) affected)

07/31/2004 WF

(1 row(s) affected)

07/31/2004 WTP
Server: Msg 2627, Level 14, State 1, Line 24
Violation of PRIMARY KEY constraint 'PK_ProcessDepSummary'. Cannot insert duplicate key in object 'ProcessDepSummary'.
The statement has been terminated.
07/31/2004 WTP

It Tries to Insert the Row of "07/31/2004 WTP" again, once the same row has already been inserted. Anybody have any ideas to how to overcome this problem? if so pls help me. All I need is to insert all value without any duplication.

Hifni

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 02:03:49

what does this do? i just change the sequence of your statements and remove the first fetch line

/*FETCHES THE FIRST RECORD*/
FETCH NEXT FROM InsertDepCursor INTO @InDepID

/*WHILE THE CURSOR COME END OF THE RECORDSET DO*/
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)
PRINT '07/31/2004' + ' ' + @InDepID
FETCH NEXT FROM InsertDepCursor INTO @InDepID
END

quote:
Originally posted by hifni

Hi,
uses: microsoft sql server 2000;

I'm using the Cursor to go through a table and insert the rows of a column to a another table. The Problem I'm facing is that in the end of message pane of the SQL Query Analyser I get a duplicate value of the last row. Below is the sql code I've wrote:

/*DECLARING A VARIABLE TO HOLD THE DEPARTMENT ID */
DECLARE @InDepID Varchar(3)

/*DECLARING A CURSOR */
DECLARE InsertDepCursor CURSOR
LOCAL SCROLL STATIC[code]
FOR
SELECT DepID FROM Departments
ORDER BY DepID

/*OPENS THE CURSOR*/
OPEN InsertDepCursor

/*FETCHES THE FIRST RECORD*/
FETCH NEXT FROM InsertDepCursor INTO @InDepID

/*INSERTS INTO THE PROCESSDEPSUMMARY TABLE WITH TODATE*/
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)

/*PRINTS THE RESULT*/
PRINT '07/31/2004' + ' ' + @InDepID

/*WHILE THE CURSOR COME END OF THE RECORDSET DO*/
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM InsertDepCursor INTO @InDepID
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)
PRINT '07/31/2004' + ' ' + @InDepID

END
CLOSE InsertDepCursor
DEALLOCATE InsertDepCursor

This is the result Message Set i got:
(1 row(s) affected)

07/31/2004 AC

(1 row(s) affected)

07/31/2004 BU

(1 row(s) affected)

07/31/2004 CD

(1 row(s) affected)

07/31/2004 CH

(1 row(s) affected)

07/31/2004 CM

(1 row(s) affected)

07/31/2004 CP

(1 row(s) affected)

07/31/2004 CR

(1 row(s) affected)

07/31/2004 CT

(1 row(s) affected)

07/31/2004 DC

(1 row(s) affected)

07/31/2004 EL

(1 row(s) affected)

07/31/2004 EMU

(1 row(s) affected)

07/31/2004 EN

(1 row(s) affected)

07/31/2004 EX

(1 row(s) affected)

07/31/2004 FI

(1 row(s) affected)

07/31/2004 FIR

(1 row(s) affected)

07/31/2004 FM

(1 row(s) affected)

07/31/2004 GM

(1 row(s) affected)

07/31/2004 GT

(1 row(s) affected)

07/31/2004 HRP

(1 row(s) affected)

07/31/2004 IM

(1 row(s) affected)

07/31/2004 IN

(1 row(s) affected)

07/31/2004 IT

(1 row(s) affected)

07/31/2004 JW

(1 row(s) affected)

07/31/2004 KG

(1 row(s) affected)

07/31/2004 LE

(1 row(s) affected)

07/31/2004 LG

(1 row(s) affected)

07/31/2004 LH

(1 row(s) affected)

07/31/2004 LR

(1 row(s) affected)

07/31/2004 LS

(1 row(s) affected)

07/31/2004 MF

(1 row(s) affected)

07/31/2004 MX

(1 row(s) affected)

07/31/2004 PE

(1 row(s) affected)

07/31/2004 PO

(1 row(s) affected)

07/31/2004 PU

(1 row(s) affected)

07/31/2004 RA

(1 row(s) affected)

07/31/2004 RBF

(1 row(s) affected)

07/31/2004 RG

(1 row(s) affected)

07/31/2004 RSP

(1 row(s) affected)

07/31/2004 SB

(1 row(s) affected)

07/31/2004 SE

(1 row(s) affected)

07/31/2004 SP

(1 row(s) affected)

07/31/2004 SR

(1 row(s) affected)

07/31/2004 TB

(1 row(s) affected)

07/31/2004 TC

(1 row(s) affected)

07/31/2004 TE

(1 row(s) affected)

07/31/2004 TM

(1 row(s) affected)

07/31/2004 TX

(1 row(s) affected)

07/31/2004 WF

(1 row(s) affected)

07/31/2004 WTP
Server: Msg 2627, Level 14, State 1, Line 24
Violation of PRIMARY KEY constraint 'PK_ProcessDepSummary'. Cannot insert duplicate key in object 'ProcessDepSummary'.
The statement has been terminated.
07/31/2004 WTP

It Tries to Insert the Row of "07/31/2004 WTP" again, once the same row has already been inserted. Anybody have any ideas to how to overcome this problem? if so pls help me. All I need is to insert all value without any duplication.

Hifni

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:04:23

All Gurus say don't use cursors.

suppose you have
tableA(aa int,bb int)
Tableb(bb)

you want to insert col bb values from tableA into TableB

Insert into Tableb(bb)
select bb from TableA

Cheers

mk_garg

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 02:14:41

Your problem is that you are running the insert twice. Rearrange your code as follows:

/*DECLARING A VARIABLE TO HOLD THE DEPARTMENT ID */
DECLARE @InDepID Varchar(3)

/*DECLARING A CURSOR */
DECLARE InsertDepCursor CURSOR LOCAL SCROLL STATIC FOR
SELECT DepID FROM Departments ORDER BY DepID

/*OPENS THE CURSOR*/
OPEN InsertDepCursor

/*FETCHES THE FIRST RECORD*/
FETCH NEXT FROM InsertDepCursor INTO @InDepID

--DELETE THIS SECTION
/*INSERTS INTO THE PROCESSDEPSUMMARY TABLE WITH TODATE*/
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID])
VALUES('07/31/2004', @InDepID)

/*PRINTS THE RESULT*/
PRINT '07/31/2004' + ' ' + @InDepID

/*WHILE THE CURSOR COME END OF THE RECORDSET DO*/
WHILE @@FETCH_STATUS = 0
BEGIN

-- AND SWAP THESE LINES
INSERT INTO ProcessDepSummary([PeriodEnd], [DepID]) VALUES('07/31/2004', @InDepID)
PRINT '07/31/2004' + ' ' + @InDepID

FETCH NEXT FROM InsertDepCursor INTO @InDepID
END

CLOSE InsertDepCursor
DEALLOCATE InsertDepCursor

The way you have the code, the last fetch gets run again after you have inserted the final record.

But really, this is a bad use of cursors, you could achieve exactly the same results with the following:

insert into ProcessDepSummary (PeriodEnd, DepID)
select '07/31/2004', depid from departments order by depid

Much quicker and simpler.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

hifni
Starting Member

4 Posts

Posted - 2004-09-10 : 22:47:25

Thanx for all of you, who came forward to help me. It's nice to have ur ideas and suggestions as well. I'm using cursor to get familiar with all of the TSQL codes, so it's just tutorial. Anyway, once again I would like to tell that your ideas and suggestions are valuable indeed.

Thank you,
Hifni

hifni
Starting Member

4 Posts

Posted - 2004-09-11 : 02:50:31

Hi,
It's me again, Just one more clarification from you all. Is there anyway, whether a queried result set could be looped through. I mean lets say I queried as "Select CustomerID, CustomerName from Customer". In that result set is it possible to write any kind of loops apart from Cursors?

Regards,
Hifni

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-11 : 07:35:57

have a look at the WHILE construct.
search here (and BOL) for examples.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-12 : 23:28:18

you could insert the result to a table data type or temp table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources