How can we run multiple SQL Scripts in SQL Server from within Windows with batch script

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

In this article we will see an easy way to be able to run SQL Scripts in bulk in the SQL Server with batch script and keep separate results for each in CSV.

The example

Let's say we have multiple scripts that we want to run in the database AdventureWorks2019 and some in StackOverflow2013.

The only thing we need to have done as preparation is to have put each script in a folder that will have the name of the base we want to run in each case and to have named them with a number at the beginning so that they are in order:

Inside the folder with the name of the base that contains the scripts, we also add the batch script (sqlcmd_batch_script.cmd) which we will see below what it should contain:

In the first script for the example, we ask that it bring us the name of the server, the name of the database and the tables it contains:

Now double click on the batch script (sqlcmd_batch_script.cmd) the command window will appear and we will see the scripts run one by one with sqlcmd:

When completed we will see that in the same folder it will have exported to CSV separately the results for each:

If we open the first CSV with the results of the first script, we will see that it saved us the results of the select we requested with the server name, the base name and the tables it contains:

The code

Let's now see step by step what it should contain batch script (sqlcmd_batch_script.cmd) and what parameters we set.

For starters, we keep it full path for each file in the folder and set the variable dbname to get the name of the folder (which will be the same as the name of the base we want it to run):

CD /d "%~dp0"

for %%I in (.) do set dbname=%%~nI

Then we define the type of file we want to export the results and the delimiter which we want it to have. In our case, we choose CSV and comma for delimiter so that when we open it it is already divided into columns:

set filetype=.csv
set delimiter=,

In the next step, we define the name of the server with the door, the username and the password with which it will run (if we used Active Directory login, the user and pass parameters should have been removed):

set instance=SMATZOURANISLP,1433
set user=stratos
set pass=password

We can have a second instance in the script and it will run in the corresponding instance depending on the name of the database:

set instance2=SMATZOURANISLP\INSTANCE2,1533
set user2=stratos
set pass2=password

In this case we define when it will connect to the first instance and when to the second:

IF "%dbname%"=="StackOverflow2013" GOTO instance1
IF "%dbname%"=="AdventureWorks2019" GOTO instance1
 

IF "%dbname%"=="StackOverflow2016" GOTO instance2

GOTO End1

So accordingly it will go to the piece of code for instance1:

:instance1


for %%G in (*.sql) do sqlcmd /S "%instance%" /d "%dbname%" -U "%user%" -P "%pass%" -i"%%G" -b -s"%delimiter%" -W -o "%%G%filetype%"

GOTO End1

or instance2:

:instance2

for %%G in (*.sql) do sqlcmd /S "%instance2%" /d "%dbname%" -U "%user2%" -P "%pass2%" -i"%%G" -b -s"%delimiter%" -W -o "%%G%filetype%"

GOTO End1


:End1

pause

The script code all together

In the end the batch script (sqlcmd_batch_script.cmd) should have this form:

CD /d "%~dp0"


for %%I in (.) do set dbname=%%~nI
set filetype=.csv
set delimiter=,


set instance=SMATZOURANISLP,1433
set user=stratos
set pass=password


set instance2=SMATZOURANISLP\INSTANCE2,1533
set user2=stratos
set pass2=password



IF "%dbname%"=="StackOverflow2013" GOTO instance1
IF "%dbname%"=="AdventureWorks2019" GOTO instance1
 

IF "%dbname%"=="StackOverflow2016" GOTO instance2


GOTO End1


:instance1


for %%G in (*.sql) do sqlcmd /S "%instance%" /d "%dbname%" -U "%user%" -P "%pass%" -i"%%G" -b -s"%delimiter%" -W -o "%%G%filetype%"

GOTO End1


 
:instance2

for %%G in (*.sql) do sqlcmd /S "%instance2%" /d "%dbname%" -U "%user2%" -P "%pass2%" -i"%%G" -b -s"%delimiter%" -W -o "%%G%filetype%"

GOTO End1


:End1

pause

Sources:

• sqlcmd Utility