Lets say you have a huge table and you want to insert all its rows into another table. To avoid pulling all of it together at one go, you can specify increments.
Here is an example. Substitute the values for the three variables in your code :
Lets say your table is called CalculatedOne and has 100 rows. Now, variable @a is the start of the loop (ie: 1). And variable @b is the no of rows in the table. And @c is your increment. For this example, we have set it to 5. For a huge table with millions of rows, you can change this to say 10,000 or 1,00,000.
NB : Our table CalculatedOne has two columns named emplid and aname. We will insert these values to a table called TMP_CalculatedOne.
Now when you execute the code below, you can see that instead of the usual “100 rows affected”, it will show “5 rows affected” for x number of times. Let me know if you need any help with this.
— Variable declaration ::
— @a is start of loop, @b is end of loop, @c is the increment value (here 5)
declare @a int, @b int, @c int
select @a=1, @b = (select COUNT(1) from CalculatedOne), @c=5
— begin looping
insert TMP_CalculatedOne select emplid, aname
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum
WHERE sub.RowNum BETWEEN @a AND @c
— reset start of loop to increment value
— increase increment by another 5