Search code examples
batch-filesqlcmd

Using sqlcmd in a batch script to run multiple scripts with output files

I need to run about 50 scripts in a folder using sqlcmd from a batch file. Each script's query results need to be sent to its own output file. I have a working batch file that just runs each from a separate line:

sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P "%PASSWORD%" -i "%SCRIPTFOLDER%\master_departments.sql" -s "|" -o "%OUTPUTFOLDER%\master_departments.csv" -W
sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P "%PASSWORD%" -i "%SCRIPTFOLDER%\master_companies.sql" -s "|" -o "%OUTPUTFOLDER%\master_companies.csv" -W
sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P %PASSWORD% -i "%SCRIPTFOLDER%\bill_history.sql" -s "|" -o "%OUTPUTFOLDER%\bill_history.csv" -W
sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P %PASSWORD% -i "%SCRIPTFOLDER%\episodes.sql" -s "|" -o "%OUTPUTFOLDER%\episodes.csv" -W

Is there any way to run this in some kind of loop? I've seen examples that run a loop of all SQL scripts in a folder, but nothing that I've seen does it with an output file set.

Per @LotPings' suggestion I used the below code:

set INSTANCE=<someinstance>
set DATABASE=<somedb>
set USERNAME=<someuser>
set PASSWORD=<somepassword>
set "SCRIPTFOLDER=D:\<pathToScripts>\"
set "OUTPUTFOLDER=D:\<pathForOutput>\"

@Echo off
For /F "tokens=*" %%S in ('Dir /B "%SCRIPTFOLDER%*.sql" '
) do echo sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P "%PASSWORD%" -i "%%~fS" -s "|" -o "%%~dpnS.csv" -W

@pause

I ran that in a batch file and when it paused, the last line said, "The system cannot find the file specified."

Thinking it was perhaps the backslashes in my paths, I removed them and put a slash before the .sql in the for line, but I got the same results.

Removing the backslash altogether resulted in a "File not found" message when I ran it like that.

Solution

  • In case your output file name matches the script name (without extension)
    and your parameters are the same for all scripts

    @Echo off
For /F "tokens=*" %%S in ('Dir /B "%SCRIPTFOLDER%*.sql" '
) do echo sqlcmd -S %INSTANCE% -d %DATABASE% -U %USERNAME% -P "%PASSWORD%" -i "%%~fS" -s "|" -o "%%~dpnS.csv" -W

    The echo in front of sqlcmd prevents execution and allows to review the output. If all looks OK, remove the echo.

    The for variable behaviour can be changed with ~ modifiers, see For /? or visit ss64.com/nt/for.html / syntax-args

    To pass a folder to the batch you can input via set /P or hand over via command line arguments.