DanBrother的部落格

A quick way to run multiple SQL files of MSSQL

Suppose we have multiple SQL files of MSSQL to be executed.
Here's a quick way to do that.

For example:
Let's create 5 SQL files that contain ready-to-execute stored procedures as follows:

echo sp_insert_test_tab 'John' , '10000001' > E:\test\a.txt
echo sp_insert_test_tab 'Bob' , '10000002' > E:\test\b.txt
echo sp_insert_test_tab 'Anthony' , '10000003' > E:\test\c.txt
echo sp_insert_test_tab 'Kathy' , '10000004' > E:\test\d.txt
echo sp_insert_test_tab 'Claire' , '10000005' > E:\test\e.txt

Next, let's create a Windows batch file called "generate_run.bat" as follows:

E:\test>copy con generate_run.bat
mkdir OUTPUT
del /Q run_sql.bat
echo pause > run_sql.bat
for %%f in (*.txt) do (
echo OSQL -S 127.0.0.1 -d testdb -E -i %%~dpnxf -o %%~dpfOUTPUT\output_%%~nxf >>
run_sql.bat
)
^Z

Finally, let's execute "generate_run.bat" to have the filename "run_sql.bat" created.

When we type the run_sql.bat , here's the content of the file.
type E:\test\run_sql.bat

pause
OSQL -S 127.0.0.1 -d testdb -E -i E:\test\a.txt -o E:\test\OUTPUT\OUTPUT_a.txt
OSQL -S 127.0.0.1 -d testdb -E -i E:\test\b.txt -o E:\test\OUTPUT\OUTPUT_b.txt
OSQL -S 127.0.0.1 -d testdb -E -i E:\test\c.txt -o E:\test\OUTPUT\OUTPUT_c.txt
OSQL -S 127.0.0.1 -d testdb -E -i E:\test\d.txt -o E:\test\OUTPUT\OUTPUT_d.txt
OSQL -S 127.0.0.1 -d testdb -E -i E:\test\e.txt -o E:\test\OUTPUT\OUTPUT_e.txt

We can simply double-click E:\test\run_sql.bat to have the five SQL files been executed.