SQL server - updating table one record at a time

Question

ok, please keep in mind that I am fairly new to SQL Server, though I have built and worked with Access databases, but I always interacted with them using DAO where creating a loop was easy-peazy.

Here is what I am trying to do, without success so far:

1) I need to update the main dynamic table with data from a history/transition table. The [SendToTbl] table is used as the primary key for the rest of the data tables in the database. The history/transition table - [SelectFromTbl] has the history of all the items in the [SendToTbl] table. This is a many-to-One relationship basically.

2) Each row of the [SelectFromTbl] is a update with the datetime stamp, so ... [SendToTbl] id # 1 might have 6 records in the [SelectFromTbl] that need to be moved one at a time.

I thought that I might be able to do this cursor - (and yes, I have read multiple posts where some think it is evil) - but since it is a one-off script that will not be used in an ongoing fashion I think the drawbacks to using a cursor are mitigated.

When I run my script, my results window shows that the cursor is moving through the [SelectFromTbl] successfully. YAY!

It is just not updating the [SendToTbl] records as expected. Grrr!

I have been beating my head against the wall trying to get this to work, and maybe it is the way that I am searching, but I am not finding a answer. The [SelectFromTbl] has over 300,000 records in it that need to be loaded one at a time - in a specific order by date ....

How the heck do I update the other table with the data? I can really use some help here to figure this out.

Here is my public version

declare 
      @MainID int
    , @myNumber int
    , @myAmt decimal(25,2)
    , @myName varchar(100)

DECLARE tCusor CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR SELECT [Main]     
              , [myNumber]
              , [myAmount]
              , [myName]
  FROM [dbo].[SelectFromTbl]

    open tCusor
    fetch next from tCusor
        into @MainID, @myNumber, @myAmt, @myName
    while (@@FETCH_STATUS = 0)
    begin

        UPDATE [dbo].[SendToTbl]
            SET [MainNum] = @myNumber
            , [MainAmt] = @myAmt
            , [MainName] = @MyName
        WHERE [MainID] =  @MainID
    fetch next from tCusor
    end
    close tCusor
    deallocate tCusor
GO

Answer 1

It's your 2nd FETCH NEXT that needed to be fixed, see below. You did the fetch, but forgot the INTO part.

declare 
  @MainID int
 , @myNumber int
 , @myAmt decimal(25,2)
 , @myName varchar(100)

DECLARE tCusor CURSOR 
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT [Main]     
          , [myNumber]
          , [myAmount]
          , [myName]
FROM [dbo].[SelectFromTbl]

open tCusor
fetch next from tCusor
    into @MainID, @myNumber, @myAmt, @myName

while (@@FETCH_STATUS = 0)
begin
    UPDATE [dbo].[SendToTbl]
        SET [MainNum] = @myNumber
        , [MainAmt] = @myAmt
        , [MainName] = @MyName
    WHERE [MainID] =  @MainID

    fetch next from tCusor
        into @MainID, @myNumber, @myAmt, @myName
end
close tCusor
deallocate tCusor