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

Question

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.

score 2 · Accepted Answer · 2018-06-05 17:31:02Z

2

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.

edited Jun 5, 2018 at 17:31

answered May 31, 2018 at 19:35

user6811411

Sign up to request clarification or add additional context in comments.

2 Comments

marky Over a year ago

There's a lot of code in there that I don't understand, but I have a question before I test it in my file: You are correct in that the output file can be the same as the script file. How does the "%%~dpnS.csv" part work? The user needs to be able to specify the output path for all of the output files to go in.

user6811411 Over a year ago

See links in my changed answer. I can't match both of your questions - maybe better solve one issue after the other.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related