Search code examples
sql-serverbatch-filebatch-processingsqlcmd

How do I call a stored procedure on each of the files in a directory as a parameter?

I'm very green with Batch scripting, but as my client doesn't allow the use of SSIS, this is the only tool I can turn to right now...

I have a folder of flat files, and I want to call a stored procedure on each file.

I can loop through the files, and I can call sqlcmd, but I can't figure out how to pass the name of the file to the stored procedure.

Here's what I have:

pushd D:\test
for /F "delims=" %%i IN ('dir /b') do sqlcmd -S servername -U username -P pword -Q "EXEC db.schema.sp $(the_filename)" -v the_filename = %%i
popd

But I'm getting an error: Incorrect syntax near '%'

What is the correct syntax?

Solution

  • Try this:

    pushd D:\test
for /F "delims=" %%i IN ('dir /b') do (
    sqlcmd -S servername -U username -P pword -Q "EXEC db.schema.sp '%%i'"
)
popd

    The parentheses are not required, but I like to use them for added clarity.