Currently, the following code in a batch file works: it runs all SQL scripts in %SCRIPTFOLDER% and sends each script's output to a CSV within the same folder as the scripts.
FOR /F "tokens=*" %%S IN (
'DIR /B "%SCRIPTFOLDER%\*.sql" '
) DO (
sqlcmd -b -S %INSTANCE% -d %DATABASE% -i "%%~fS" -s "|" -o "%%~dpnS.csv" -W
IF ERRORLEVEL 1 GOTO errorhandling
ECHO %%~nS.csv successfully created
)
What I'd like to do is allow the user to specify where the generated CSVs get sent to using a variable %OUTPUTFOLDER%.
I tried placing %OUTPUTFOLDER%, which is a full path, drive, and folders (e.g. D:\some folder\output) in various positions within %%~dpnS.csv. Specifically,
%%~dp%OUTPUTFOLDER%nS.csv
and
%%~dpn%OUTPUTFOLDER%S.csv
but they didn't work and I'm (probably obviously to you) woefully inept at batch file syntax!
I understand that dp is the drive and path and that S is the file name, but I'm not sure how to integrate that with a the variable that is the path.
The iterating variable is %%S, the modifier ~dpn forces an evaluation of drive path and name.
In this case you want to specify the drive and path yourself so depending on wether %OUTPUTFOLDER% has a trailing backslash
-o "%OUTPUTFOLDER%%%~nS"
or not use:
-o "%OUTPUTFOLDER%\%%~nS"