sql cursor repeating

Question

I'm having trouble with a cursor I'm using in SQL. This is my first attempt at using one, but it seems to be infinitely repeating the first entry rather than iterating through and I'm unsure why this is, as my understanding was that the fetch next statement should continue through the loop.

The output shows as:

(235 row(s) affected)
95208

(1 row(s) affected)

(1 row(s) affected)
95208

(1 row(s) affected)

(1 row(s) affected)
95208

(1 row(s) affected)
...

Any help would be greatly appreciated.

Here is my script:

Disable trigger tr_ttcard_ForUpdate on ttcard

-- Loop through backupBoards
declare @getTBoard cursor
declare @getTCard cursor    
declare @tcardToUpdateId int

select ttboard.id into #TempTBoards from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
where ttboard.IsActive = 0 and newtbackuptboards.id is not null

-- Update each existing tcard in backupBoards to new mappings
set @getTCard = cursor for
select ttcard.id from ttcard 
join #TempTBoards on #TempTBoards.id = ttcard.parentboard
where #TempTBoards.id is not null

open @getTCard
fetch next from @getTCard into @tcardToUpdateId
while @@FETCH_STATUS = 0
begin
    print cast(@tcardToUpdateId as nvarchar(max))
    -- create new backupCardMap linking tcardId and backupId
    insert into tbackupTCardMap (backupTCardId, backupId)
    values(
        @tcardToUpdateId,
        (select newtbackuptboards.id from newtbackuptboards 
         join ttboard on ttboard.id = newtbackuptboards.TBoardId
         join ttcard on ttcard.parentboard = ttboard.id
         where ttcard.id = @tcardToUpdateId)
    )

    update ttcard
    set backupOfTCard = @tcardToUpdateId
    where id = @tcardToUpdateId

end
close @getTCard
deallocate @getTCard

drop table #TempTBoards
go
-- Enable trigger for tcard changes
Enable trigger tr_ttcard_ForUpdate on ttcard

Answer 1

I would replace this performance killer cursor with simple INSERT and UPDATE statements, something like this....

BEGIN TRANSACTION;

-- Update records
update ttcard
set backupOfTCard = ttboard.id
from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
join ttcard            on ttcard.parentboard = ttboard.id 
where ttboard.IsActive = 0 
  AND newtbackuptboards.id IS NOT NULL

-- Insert records
insert into tbackupTCardMap (backupTCardId, backupId)
SELECT ttboard.id , newtbackuptboards.id
from ttboard 
join newtbackuptboards on newtbackuptboards.TBoardId = ttboard.id
join ttcard            on ttcard.parentboard = ttboard.id 
where ttboard.IsActive = 0 
  AND newtbackuptboards.id IS NOT NULL

COMMIT TRANSACTION;