Reputation: 7777
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.
Upvotes: 1
Views: 219
Reputation: 23123
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
Upvotes: 1
Reputation: 51694
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 ...
Upvotes: 1