Here’s another one of those sneaky little tricks that can be used to make your life easier.

I found myself needing to perform an insert of 240 million records from one table to another in the same database today. Rather than try to do this all in one transaction I wanted to keep the transaction log nice and small and so decided to do this in small batches (the database is in simple recovery).

Each batch would insert 50,000 records, with it only taking a few seconds to do each batch. Rather than sit there and have to execute this constantly I decided to throw the insert into a while loop with a begin/commit transaction and a checkpoint. I also wanted to know how many loops had been processed but didn’t want to overwhelm myself with data by printing every loop that was processed.

In the end I decided that knowing every 10 loops was more than enough information and so I put the modulo operator to good use.

In case you aren’t familiar with modulo (%) it simply returns the remainder when one number is divided by another. For example

SELECT 25 % 3

returns 1

In this case as I wanted to find out when the loop was divisible by 10 I just checked to see when the loop counter divided by 10 equaled zero (as there would be no remainder).

I threw that into my code and was able to quickly see how far along my processing was.

Here’s a quick example that you can just run to see what I mean.

DECLARE @msg NVARCHAR(255) ,

    @loopno INT = 0

WHILE @loopno < 1000 

    BEGIN

        IF ( @loopno % 10 ) = 0 

            BEGIN

                SELECT  @msg = 'Processing loop number '

                        + CONVERT(NVARCHAR, @loopno)

                RAISERROR (@msg, 0, 1) WITH NOWAIT

            END

        SET @loopno += 1

    END

As a DBA I really don’t think of using modulo often but it’s actually very useful.