Query not working fine in while loop

问题:

I have a While loop where I am trying to insert.

              DECLARE @CurrentOffer int  =121
        DECLARE @OldestOffer int  = 115
        DECLARE @MinClubcardID bigint=0
              DECLARE @MaxClubcardID bigint=1000
                    WHILE 1 = 1
                        BEGIN
                        INSERT INTO Temp WITH (TABLOCK)
                        SELECT  top (100) clubcard  from TempClub   with (nolock) where ID between 
                        @MinClubcardand and @MaxClubcard

                        declare @sql varchar (8000)
                        while @OldestOffer <= @CurrentOffer
                        begin
                        print @CurrentOffer
                        print @OldestOffer

                                set @sql = 'delete from Temp where Clubcard 
                                 in (select Clubcard from ClubTransaction_'+convert(varchar,@CurrentOffer)+' with (nolock))'
                                 print (@sql)
                                 exec (@sql)

                                SET @CurrentOffer = @CurrentOffer-1  
                                IF @OldestOffer = @CurrentOffer
                                    begin

                                        -- my logic
                                    end

                        end
                    end

My TempClub table always checks only with first 100 records. My TempClub table has 3000 records. I need to check all my clubcard all 3000 records with ClubTransaction_121,ClubTransaction_120,ClubTransaction_119 table.

回答1:

The SELECT query in line 8 returns only the top 100 items

SELECT top (100) clubcard from TempClub ...

If you want to retrieve all items, remove the top (100) part of your statement

SELECT clubcard from TempClub ...

回答2:

In order to do batch type processing, you need to set the @MinClubcardID to the last ID processed plus 1 and include an ORDER BY ID to ensure that the records are being returned in order.

But... I wouldn't use the approach of using the primary key as my "index". What you're looking for is a basic pagination pattern. In SQL Server 2005+, Microsoft introduced the row_number() function which makes pagination a lot easier.

For example:

 DECLARE @T TABLE (clubcard INT)

 DECLARE @start INT
 SET @start = 0

 WHILE(1=1)
 BEGIN
   INSERT @T (clubcard)
   SELECT TOP 100 clubcard FROM 
   (
      SELECT clubcard,
      ROW_NUMBER() OVER (ORDER BY ID) AS num
      FROM dbo.TempClub
   ) AS t
   WHERE num > @start

  IF(@@ROWCOUNT = 0) BREAK;

  -- update counter
  SET @start = @start + 100

  -- process records found

  -- make sure temp table is empty
  DELETE FROM @T
END