Getting there...
An extension of my last question. Got a few spare minutes, kludged it together very quick, and as of right now it's doing what it's supposed to do right well. Problem is, what it's doing is not exactly what I want it to do (and it's taking about forever). I'm hoping someone who knows more about cursors can solve both problems in one fell swoop.
DECLARE @junk varchar(255), @recnum int, @mrecord int
set @mrecord = 1
DECLARE myCursorName CURSOR LOCAL FAST_FORWARD
FOR
select * from stagingtest
OPEN myCursorName
-- Get the first row from the cursor
FETCH NEXT from myCursorName INTO @junk, @recnum
WHILE @@FETCH_STATUS = 0
BEGIN
if left(@junk,3) in ('INS','COB')
begin
set @mrecord = @mrecord + 1
end
--replace recnum with @mrecord
update stagingtest set recnum = @mrecord where junk = @junk
-- Get the next row from the cursor
FETCH NEXT from myCursorName INTO @junk, @recnum
END
CLOSE myCursorName
DEALLOCATE myCursorName
My problem is that instead of writing back to the line in the table that mirrors the one I just pulled from the cursor, the update query is running over the whole table, which (a) is making it unconscionably long to run (I killed it this morning at twenty-two hours), and (b), more importantly-- not all the lines in this table are unique. So for example, record 2 in this table, which is ten lines long, comes out with the following line numbers on those ten lines: 99,2,29,2,3,3,2,99,3,29, instead of a nice, neat column of 2s.
Short of changing the data coming in to make each line unique, which would pose its own set of problems later on, is there any way to get a query to write only to that specific line of the table instead of searching the whole table for lines to update?
EDIT: I added "...and recnum is null" to the update query, which fixed about 3/4 of the problems-- obviously, the system isn't gonig to go back to find records if they're all written, but the records it searches forward to find are still getting hit (for example, record 3, which is fifteen lines, now has the record numbers 3,3,2,3,2,2,3,2,2,2,3,3,3,3,1 instead of that neat and tidy row of 3s).
Anyone have any suggestions? TIA.
DECLARE @junk varchar(255), @recnum int, @mrecord int
set @mrecord = 1
DECLARE myCursorName CURSOR LOCAL FAST_FORWARD
FOR
select * from stagingtest
OPEN myCursorName
-- Get the first row from the cursor
FETCH NEXT from myCursorName INTO @junk, @recnum
WHILE @@FETCH_STATUS = 0
BEGIN
if left(@junk,3) in ('INS','COB')
begin
set @mrecord = @mrecord + 1
end
--replace recnum with @mrecord
update stagingtest set recnum = @mrecord where junk = @junk
-- Get the next row from the cursor
FETCH NEXT from myCursorName INTO @junk, @recnum
END
CLOSE myCursorName
DEALLOCATE myCursorName
My problem is that instead of writing back to the line in the table that mirrors the one I just pulled from the cursor, the update query is running over the whole table, which (a) is making it unconscionably long to run (I killed it this morning at twenty-two hours), and (b), more importantly-- not all the lines in this table are unique. So for example, record 2 in this table, which is ten lines long, comes out with the following line numbers on those ten lines: 99,2,29,2,3,3,2,99,3,29, instead of a nice, neat column of 2s.
Short of changing the data coming in to make each line unique, which would pose its own set of problems later on, is there any way to get a query to write only to that specific line of the table instead of searching the whole table for lines to update?
EDIT: I added "...and recnum is null" to the update query, which fixed about 3/4 of the problems-- obviously, the system isn't gonig to go back to find records if they're all written, but the records it searches forward to find are still getting hit (for example, record 3, which is fifteen lines, now has the record numbers 3,3,2,3,2,2,3,2,2,2,3,3,3,3,1 instead of that neat and tidy row of 3s).
Anyone have any suggestions? TIA.
