DOS batch file - loop and increment by 1

Question

I have this batch file that logs into sql on a remote machine runs a stored procedure and then sends the output to a text file. I would like it to increment both the 3rd octet in the IP address and the name of the output text file by 1 and loop so I don't have to repeat the command over and over. Also, I would like it to stop when it reaches a certain number. Is there a way to do this?

sqlcmd -U user -P password -S 192.168.1.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput01.txt
sqlcmd -U user -P password -S 192.168.2.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput02.txt
sqlcmd -U user -P password -S 192.168.3.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput03.txt
sqlcmd -U user -P password -S 192.168.4.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput04.txt
sqlcmd -U user -P password -S 192.168.5.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput05.txt

Solution 1

this will do what you want. the /L specifier tells it to act like a regular programming for loop. the first parameter is the starting integer, the next is the number to step, and the last is the count. so this will loop starting at 1, incrementing by 1 for 6 integers:

@echo off
FOR /L %%G IN (1, 1, 6) DO (
     echo sqlcmd -U user -P password -S 192.168.%%G.2 -i c:\sql\storecreditfix.sql -o c:\sql\ouput0%%G.txt
)

and if you want to do a list of IPs instead of a range, you can leave off the /L and just list the numbers. e.g. FOR %%G IN (1,2,3,99,121) DO ...

obviously the "echo" before sqlcmd is just for testing;)

Solution 2

On the assumption that you're actually using cmd.exe rather than MS-DOS, one way to increment and test a variable is as follows:

    @setlocal enableextensions enabledelayedexpansion
    @echo off
    set /a "i = 1"
:loop
    if !i! leq 15 (
        if !i! lss 10 (
            echo sqlcmd -S 192.168.!i!.2 -o c:\sql\ouput0!i!.txt
        ) else (
            echo sqlcmd -S 192.168.!i!.2 -o c:\sql\ouput!i!.txt
        )
        set /a "i = i + 1"
        goto :loop
    )
    endlocal

This is a slightly modified version of what you need which echoes the relevant bits rather than executing them, and it outputs:

sqlcmd -S 192.168.1.2 -o c:\sql\ouput01.txt
sqlcmd -S 192.168.2.2 -o c:\sql\ouput02.txt
sqlcmd -S 192.168.3.2 -o c:\sql\ouput03.txt
sqlcmd -S 192.168.4.2 -o c:\sql\ouput04.txt
sqlcmd -S 192.168.5.2 -o c:\sql\ouput05.txt
sqlcmd -S 192.168.6.2 -o c:\sql\ouput06.txt
sqlcmd -S 192.168.7.2 -o c:\sql\ouput07.txt
sqlcmd -S 192.168.8.2 -o c:\sql\ouput08.txt
sqlcmd -S 192.168.9.2 -o c:\sql\ouput09.txt
sqlcmd -S 192.168.10.2 -o c:\sql\ouput10.txt
sqlcmd -S 192.168.11.2 -o c:\sql\ouput11.txt
sqlcmd -S 192.168.12.2 -o c:\sql\ouput12.txt
sqlcmd -S 192.168.13.2 -o c:\sql\ouput13.txt
sqlcmd -S 192.168.14.2 -o c:\sql\ouput14.txt
sqlcmd -S 192.168.15.2 -o c:\sql\ouput15.txt

Simply take the echo off the line and adjust the command to put back the other bits.

Comments

[Vinnie]

This should work once you take out the echo, but you may think about using START /WAIT depending on how long your queries take.