Using while loop in sql

Question

I am trying to loop through my records with a where clause.

I am trying to get only top 100 rows first and then next 100 (some logic i apply with what i get in the select clause)

But, if the first 100 rows does not return a result, it does not goes into 2nd 100 set of data.

My query is :

DECLARE @BatchSize INT = 100
DECLARE @Counter INT = 0
DECLARE @TableCount INT = 0
set @TableCount = (select count(*) from Table1)  //@TableCount = 10000

while @Counter < @TableCount/@BatchSize //@Counter < 100
BEGIN        
    SET @Counter=@Counter+1
    INSERT INTO Table4
    SELECT TOP(@BatchSize) * FROM Table2 
    WHERE NOT EXISTS (SELECT * Table3) and some condition

Here, If i dont get the data for first 100 rows, it wont go to next 100 set of data.

What should I do ?

Loops and SQL are usually an indication you're not thinking in terms of Sets. You really want to think in terms of Sets. — Joel Coehoorn
– Joel Coehoorn, Commented May 16, 2016 at 21:44
@JoelCoehoorn Can you explain me more about this. I didnt understood, I am doing loops because I have millions of rows of data and i dont want to lock the whole table. — devam demo
– devam demo, Commented May 16, 2016 at 21:45
"I want to move the data in other table" Try a SELECT INTO query instead. — Joel Coehoorn
– Joel Coehoorn, Commented May 16, 2016 at 21:47

Joel Coehoorn · Accepted Answer · 2016-05-16 21:45:46Z

1

Instead of TOP @BatchSize in the SELECT clause, try OFFSET @BatchSize * @Counter FETCH NEXT @Batchsize ROWS ONLY after the WHERE clause.

Based on comments, you may also want to look into a SELECT INTO query, as well as the nolock query hint.

answered May 16, 2016 at 21:45

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

devam demo Over a year ago

I edited the question, Can you let me know how should I use OFFSET here ? Or select into ?

JamieD77 Over a year ago

don't you need ORDER BY for OFFSET?

Joel Coehoorn Over a year ago

@JamieD77 Yes, but you need an ORDER BY to guarantee consistency for his loop, too.

devam demo Over a year ago

Ok, I got the anser by offset, so what do you suggest ? I should not go with it ? Or select into ?

devam demo Over a year ago

@JoelCoehoorn The Select Into will lock the whole database. I dont want that

Collectives™ on Stack Overflow

Using while loop in sql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related