Good use for a cursor?

Question

Post reply

Good use for a cursor?

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
September 23, 2005 at 9:44 am

#89297

Hello gurus,
I've never used cursors because I've always heard how "wrong" they are.. but that they are very useful in limited situations. Maybe someone can help me decide if my current situation is a good candidate?
I've got table 1 with Name,
ClockNumber,
Default_Line_Number,
Default_OperationNumber and
Start_date
Table 2 will be used to update some of the data in table 1.
Table 2 contains ClockNumber (the join field),
Default_Operation_Number and
Default_Line_Number.
Table 2 only contains Default_Line_Numbers and Default_Operation_Numbers for employees that need to be updated.
Is this a good candidate for a cursor or is there a set-based approach?
Thanks very much for any insight you can provide!
Bob

Viewing 6 posts - 1 through 5 (of 5 total)

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

Merrill Aldrich SSCrazy Points: 2358 More actions · Answer 1

Bob - don't think a cursor is needed; I would look at using Update ... From to accomplish this.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Yup, no cursor needed at all. Also are you doing this to transfer data? Because I don't see the point of having the same values in 2 different tables!!

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 3

I'm not keeping it in two different tables. Someone is manually filling out a spreadsheet that will contain updated information for the table.

I've got 600+ employees in the SQL Server table and the spreadsheet will contain data for about 500 of them. How do I update data for each employee (one row per employee) without a) having 500 update statements or b) using a cursor?

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 4

SET NOCOUNT ON

DECLARE @Table1 TABLE

(

[Name] VARCHAR(100),

ClockNumber INT,

Default_Line_Number INT,

Default_OperationNumber INT,

Start_date DATETIME

)

INSERT @Table1

SELECT 'Name1', 1, 11, 21, '01/01/2005' UNION

SELECT 'Name2', 2, 12, 22, '01/02/2005' UNION

SELECT 'Name3', 3, 13, 23, '01/03/2005' UNION

SELECT 'Name4', 4, 14, 24, '01/04/2005' UNION

SELECT 'Name5', 5, 15, 25, '01/05/2005' UNION

SELECT 'Name6', 6, 16, 26, '01/06/2005' UNION

SELECT 'Name7', 7, 17, 27, '01/07/2005'

DECLARE @Table2 TABLE

(

ClockNumber INT,

Default_Line_Number INT

)

INSERT @Table2

SELECT 1, 41 UNION

SELECT 4, 44 UNION

SELECT 5, 45 UNION

SELECT 7, 47

UPDATE A

SET

A.Default_Line_Number = B.Default_Line_Number

FROM

@Table1 A

JOIN

@Table2 B

ON

A.ClockNumber = B.ClockNumber

SELECT * FROM @Table1

Regards,
gova

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 5

Many thanks for the lesson... especially to govinn for the example... your example helped me perfectly!

Bob