DECLARE @sNumber VARCHAR(18)
DECLARE @nID INTEGER

WHILE EXISTS(SELECT 1 FROM Import WHERE NewCardNum IS NULL) BEGIN
   SET @sNumber = (SELECT TOP 1 CardNumber FROM NewCardNums WHERE Status = 0)
   SET @nID = (SELECT TOP 1 ID FROM Import WHERE NewCardNum IS NULL)
   UPDATE Import
     SET NewCardNum = @sNumber
     WHERE [ID] = @nID
   UPDATE NewCardNums
     SET STATUS = 1
     WHERE CardNumber = @sNumber
END

-- Create two tables to hold the data we are manipulating.  Adding an Identity column let's us join the two tables
-- row 1 to row 1, row 2 to row 2 etc.
DECLARE @tmpNewCardNums TABLE
  (Ident INT IDENTITY, CardNumber VARCHAR(18),Status INT)
DECLARE @tmpImport TABLE
  (Ident INT IDENTITY, NewCardNum VARCHAR(18) NULL, ID INT)

-- now, go round the existing loop while empty newcardnums exist
WHILE EXISTS(SELECT 1 FROM Import WHERE NewCardNum IS NULL) BEGIN
	SET ROWCOUNT 1000    -- set this number to be the batch size to process each iteration

        -- load the next lot of Import records into our temporary holding area
	INSERT INTO @tmpImport (newcardnum,id) 
		SELECT newcardnum,id FROM Import WHERE NewCardNum IS NULL

        -- and do the same with the new card numbers
	INSERT INTO @tmpNewCardNums (cardnumber,status) 
		SELECT cardnumber,status FROM newcardnums WHERE Status=0

        -- turn the rowcount limiter off
	SET ROWCOUNT 0

        -- now update the master import table by linking through the temporary batch table on the IDENTITY columns
	UPDATE Import
		SET newcardNum=cardnumber
		FROM @tmpnewcardnums TC INNER JOIN @tmpImport TI on TI.Ident=TC.Ident
		WHERE Import.ID=TI.ID

        -- now update the status of all the card numbers we've used
	UPDATE newcardnums
		SET Status=1
		WHERE cardnumber IN (SELECT cardnumber from @tmpnewcardnums)
END