2

I have a bunch of written sql scripts that I have written and I was looking to run as a batch in order in a folder. After reading up I've resorted to creating a bat file which includes using the sqlcmd. For this particular set of scripts, when i run the bat it doesn't seem to run in order. I have no idea what is going on as I've tried renaming the sql scripts numerically with a prefix number in the beginning, tried using letters, and even renaming the whole script to just a number/letter using windows explorer.

The process might look something like this A.sql - B.sql - C.sql - D.sql - F.sql - G.sql - E.sql

Any ideas as to what I am missing or how i am renaming these files incorrectly? Thanks

what I have in my bat file

for %%G in (*.sql) do sqlcmd /S W-VAN-A124178\HAZUSPLUSSRVR /d 
BC_Exposure_2016 -E -i"%%G"
pause
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Will C.
  • 41
  • 6
  • I think you may need to run multiple bat files as pointed out here (https://stackoverflow.com/questions/2071408/how-to-run-multiple-batch-files-in-serial-in-windows-command-line-environment) or run through sql agent – TheGameiswar Sep 19 '17 at 05:58
  • If you want to order the files, powershell might be a better (and more modern) alternative – Nick.Mc Sep 19 '17 at 07:22

1 Answers1

0

You could parse the output of the dir command which you can modify so that the files appear in alphabetical order. For that you could use dir /A-D /B /ON:
/A-D excludes directories
/B excludes a lot of messy additional information that is annoying to work around while parsing
/ON orders by name

So to modify your code:

for /f %%G in ('dir /A-D /B /ON') do (
  sqlcmd /S W-VAN-A124178\HAZUSPLUSSRVR /d BC_Exposure_2016 -E -i"%%G"
)
pause

should work. I put parenthesis around the loop. For once because I am used to it and because the code was split into two lines else.

geisterfurz007
  • 5,292
  • 5
  • 33
  • 54