CODEWITHSUNDEEP
batch-fileexecute-sql-task
Der_V
Der_V

Reputation: 177

How to execute only all SQL files of which name starts with a two digit number in a folder and ignore all other SQL files?

I have the following batch code which executes all SQL files of a folder.

for /r %%G in (*.sql) do sqlcmd /S localhost /d superDB /U sa /P topsecret -i"%%G"
pause

But now I want to execute only the files which start with a two digit number in name like:

01_file.sql
02_file.sql
05_file.sql
43_file.sql

But not files like:

optional_01_file.sql 
XYZ04_file.sql

Can someone tell me how to change the code to filter only files with a file name which starts with 2 digits?

Upvotes: 2

Views: 983

Answers (3)

Mofi
Mofi

Reputation: 49167

I suggest following batch file for this task:

@for /F "delims=" %%G in ('dir "%~dp0??_*.sql" /A-D-H /B /ON 2^>nul ^| %SystemRoot%\System32\findstr.exe /B /R "[0123456789][0123456789]"') do @sqlcmd.exe /S localhost /d superDB /U sa /P topsecret -i"%~dp0%%G"

This batch file expects that the *.sql files starting with [0-9][0-9]_ are stored in same directory as the batch file and the current directory on running the batch file can be any directory which is useful on running this batch file as scheduled task on which the Start in option of scheduled task is not set.

To process the *.sql files in current directory instead of directory of the batch file just remove both occurrences of %~dp0 which expands to drive and path of the batch file. The expanded batch file path string always ends with a backslash.

Both %~dp0 can be replaced also by any other folder path ending with a backlash.

The command FOR with the used options runs in a separate command process started with cmd /C in background the command line

dir "C:\Batch File Path\??_*.sql" /A-D-H /B /ON 2>nul | C:\Windows\System32\findstr.exe /B /R /C:[0123456789][0123456789]

The command DIR searches for

  • just non hidden files because of /A-D-H (attribute NOT directory and NOT hidden)
  • in directory of the batch file
  • for files matching the wildcard pattern ??_*.sql
  • and outputs just file name + file extension to handle STDOUT (standard output) because of /B (bare format)
  • order alphabetically by name because of /ON.

DIR is an internal command of cmd.exe already running for processing this batch file.

An error message output by DIR on not finding any file name matching the criteria written to handle STDERR (standard error) is redirected with 2>nul to device NUL to suppress it.

The output of DIR to handle STDOUT is redirected with | to handle STDIN (standard input) of next command FINDSTR being an external command which means a console executable available by default in Windows system directory.

FINDSTR searches for lines read from STDIN which

  • begin because of /B
  • with a string matching [0123456789][0123456789]
  • which is interpreted as regular expression search string because of using explicitly /R.

See What are the undocumented features and limitations of the Windows FINDSTR command? for the reason using [0123456789][0123456789] instead of [0-9][0-9] because of [0-9] matches also the three characters ¹²³ on being encoded with code page Windows-1252.

FINDSTR outputs all lines (= file names) matching the regular expression applied to only the first two characters to handle STDOUT of background command process.

FOR captures those file names and process them line by line. delims= defines an empty list for string splitting to assign always full name of file to loop variable G even if the *.sql file name contains one or more spaces.

Read also the Microsoft article about Using Command Redirection Operators for an explanation of | and 2>nul. The redirection operators > and | must be escaped with caret character ^ on FOR command line to be interpreted as literal character when Windows command interpreter processes this command line before executing command FOR which executes the embedded dir command line with findstr with using a separate command process started in background.

Best would be to specify also sqlcmd.exe with full path on running this batch file as scheduled task for making the batch file independent on environment variable PATH of the environment (account) used for running the scheduled task.

It is a bit unclear if just *.sql files starting with two digits and an underscore in a folder or recursively in a folder and all its subfolders should be executed with sqlcmd.

Here is a variant of above batch file command line for running all *.sql starting with [0-9][0-9]_ in entire folder tree starting with folder containing the batch file:

@for /F "delims=" %%G in ('dir "%~dp0??_*.sql" /A-D-H /B /ON /S 2^>nul ^| %SystemRoot%\System32\findstr.exe /R /C:"\\[0123456789][0123456789]_[^\\]*$"') do @sqlcmd.exe /S localhost /d superDB /U sa /P topsecret -i"%%G"

The command DIR is used additionally with option /S which results in searching also in subdirectories for files matching the wildcard pattern ??_*.sql.

The output by DIR changes with /S as the file names are output now with complete file path.

For that reason FINDSTR is without /B and the regular expression is modified to \\[0123456789][0123456789]_[^\\]*$ to get just output the *.sql files starting with two digits in file name independent on file path.

The command sqlcmd is called just with %%G as the loop variable holds the full qualified file name (= file path + file name + file extension) of the current SQL file to use.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

  • dir /?
  • findstr /?
  • for /?
  • sqlcmd -?

Upvotes: 0

aschipfl
aschipfl

Reputation: 34979

I would filter the files by name using findstr, like this:

for /F "delims=" %%G in ('
    dir /S /B /A:-D "*.sql" ^| ^
        findstr /I /R /C:"\\[0123456789][0123456789]_[^\\]*\.sql$"
') do (
    sqlcmd /S localhost /d superDB /U sa /P topsecret -i"%%G"
)
pause

Upvotes: 0

user6811411
user6811411

Reputation:

Findstr's rudimentary RegEx capabilities are sufficient to match the files.
UnTested:

:: Q:\Test\2018\07\21\SO_51456661.cmd
@echo off
for /r %%G in (*.sql) do (
    Echo=%%~nG|Findstr "^[0-9][0-9]_" 2>&1>Nul && (
        echo sqlcmd /S localhost /d superDB /U sa /P topsecret -i"%%G"
    )
)

In a folder with these files

> dir /B *.sql
01_file.sql
0815_file.sql
55_file.sql
abc0855_file.sql

I get these commands (only echoed for demonstration)

> SO_51456661.cmd
sqlcmd /S localhost /d superDB /U sa /P topsecret -i"Q:\Test\2018\07\21\01_file.sql"
sqlcmd /S localhost /d superDB /U sa /P topsecret -i"Q:\Test\2018\07\21\55_file.sql"

Upvotes: 3

Related Questions