Keith Rull

Here is some samples on how to use cursors in SQL to do updates and deletes on records. Nothing fancy, just pure t-sql code. :)

Updating records using cursors

SET NOCOUNT ON
Declare @AccountNumber varchar(20)

DECLARE AccountNumbers CURSOR FOR
(    select AccountNumber from OldAccountsTable
    where AccountNumber between '0807200' and '0807999'
)

OPEN AccountNumbers

    FETCH NEXT FROM AccountNumbers INTO @AccountId

    WHILE @@FETCH_STATUS = 0
        BEGIN

            UPDATE AccountTable
            SET AccountType = 3
            WHERE AccountNumber =@AccountNumber

            FETCH NEXT FROM AccountNumbers INTO @AccountNumber
        END
CLOSE AccountNumbers
DEALLOCATE AccountNumbers

--EXAMPLE TWO
SET NOCOUNT ON
Declare @AccountNumber varchar(20)
DECLARE @SqlStatement varchar(8000)

DECLARE AccountNumbers CURSOR FOR
(    select AccountNumber from OldAccountsTable
    where AccountNumber between '0807200' and '0807999'
)

OPEN AccountNumbers

    FETCH NEXT FROM AccountNumbers INTO @AccountId

    WHILE @@FETCH_STATUS = 0
        BEGIN
            
            --SET THE STATEMENT TO EXECUTE
            SET @SqlStatement =
            'UPDATE AccountTable
            SET AccountType = 3
            WHERE AccountNumber =' + @AccountNumber
            
            --EXECUTE THE STATEMENT
            exec(@SqlStatement)

            --PRINT THE STATEMENT IF AN ERROR HAS BEEN ENCOUNTERED
            IF (@@ERROR <> 0)
                print @SqlStatement

            FETCH NEXT FROM AccountNumbers INTO @AccountNumber
        END
CLOSE AccountNumbers
DEALLOCATE AccountNumbers

Deleting records using cursors

-- EXAMPLE ONE
SET NOCOUNT ON
Declare @AccountNumber varchar(20)

DECLARE AccountNumbers CURSOR FOR
(    select AccountNumber from OldAccountsTable
    where AccountNumber between '0807200' and '0807999'
)

OPEN AccountNumbers

    FETCH NEXT FROM AccountNumbers INTO @AccountId

    WHILE @@FETCH_STATUS = 0
        BEGIN

            DELETE FROM AccountTable
            WHERE AccountNumber =@AccountNumber

            FETCH NEXT FROM AccountNumbers INTO @AccountNumber
        END
CLOSE AccountNumbers
DEALLOCATE AccountNumbers

-- EXAMPLE TWO
SET NOCOUNT ON
Declare @AccountNumber varchar(20)

DECLARE AccountNumbers CURSOR FOR
(    select AccountNumber from OldAccountsTable
    where AccountNumber between '0807200' and '0807999'
)

OPEN AccountNumbers

    FETCH NEXT FROM AccountNumbers INTO @AccountId

    WHILE @@FETCH_STATUS = 0
        BEGIN

            --SET THE STATEMENT TO EXECUTE
            SET @SqlStatement =
            'DELETE FROM AccountTable
            WHERE AccountNumber=' @AccountNumber

            --EXECUTE THE STATEMENT
            exec(@SqlStatement)

            --PRINT THE STATEMENT IF AN ERROR HAS BEEN ENCOUNTERED
            IF (@@ERROR <> 0)
                print @SqlStatement

            FETCH NEXT FROM AccountNumbers INTO @AccountNumber
        END
CLOSE AccountNumbers
DEALLOCATE AccountNumbers