Inserting in smaller batches / increments

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
while @a<=@b
begin
insert TMP_CalculatedOne select emplid, aname
FROM
(
SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNum
FROM CalculatedOne
) sub
WHERE sub.RowNum BETWEEN @a AND @c
— reset start of loop to increment value
— increase increment by another 5
set @a=@c+1
set @c=@c+5
end

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s