{"id":4028,"date":"2023-06-16T07:00:00","date_gmt":"2023-06-16T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4028"},"modified":"2024-01-08T02:36:47","modified_gmt":"2024-01-07T23:36:47","slug":"pos-mporoyme-na-ektelesoyme-pollapla-sql","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-ektelesoyme-pollapla-sql\/","title":{"rendered":"How can we run multiple SQL Scripts in SQL Server from within Windows with batch script"},"content":{"rendered":"

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

The example<\/h2>\n\n\n\n

Let's say we have multiple scripts that we want to run in the database AdventureWorks2019 <\/em>and some in StackOverflow2013<\/em>. <\/p>\n\n\n\n

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:<\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

Inside the folder with the name of the base that contains the scripts, we also add the batch script<\/strong> (sqlcmd_batch_script.cmd<\/em>) which we will see below what it should contain:<\/p>\n\n\n\n

\"\"
02<\/figcaption><\/figure>\n\n\n\n

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:<\/p>\n\n\n\n

\"\"
03<\/figcaption><\/figure>\n\n\n\n

Now double click on the batch script<\/strong> (sqlcmd_batch_script.cmd<\/em>) the command window will appear and we will see the scripts run one by one with sqlcmd<\/a>:<\/p>\n\n\n\n

\"\"
04<\/figcaption><\/figure>\n\n\n\n

When completed we will see that in the same folder it will have exported to CSV <\/strong>separately the results for each:<\/p>\n\n\n\n

\"\"
05<\/figcaption><\/figure>\n\n\n\n

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:<\/p>\n\n\n\n

\"\"
06<\/figcaption><\/figure>\n\n\n\n

The code<\/h2>\n\n\n\n

Let's now see step by step what it should contain batch script<\/strong> (sqlcmd_batch_script.cmd<\/em>) and what parameters we set.<\/p>\n\n\n\n

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

CD \/d \"%~dp0\"\n\nfor %%I in (.) do set dbname=%%~nI\n<\/code><\/pre>\n\n\n\n
Then we define the type of file we want to export the results and the delimiter <\/strong>which we want it to have. In our case, we choose CSV <\/strong>and comma <\/strong>for delimiter so that when we open it it is already divided into columns:<\/p>\n\n\n\n
set filetype=.csv\nset delimiter=,<\/code><\/pre>\n\n\n\n
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):<\/p>\n\n\n\n
set instance=SMATZOURANISLP,1433\nset user=stratos\nset pass=password<\/code><\/pre>\n\n\n\n
We can have a second instance in the script and it will run in the corresponding instance depending on the name of the database:<\/p>\n\n\n\n
set instance2=SMATZOURANISLP\\INSTANCE2,1533\nset user2=stratos\nset pass2=password<\/code><\/pre>\n\n\n\n
In this case we define when it will connect to the first instance and when to the second:<\/p>\n\n\n\n
IF \"%dbname%\"==\"StackOverflow2013\" GOTO instance1\nIF \"%dbname%\"==\"AdventureWorks2019\" GOTO instance1\n \n\nIF \"%dbname%\"==\"StackOverflow2016\" GOTO instance2\n\nGOTO End1<\/code><\/pre>\n\n\n\n
So accordingly it will go to the piece of code for instance1:<\/p>\n\n\n\n
:instance1\n\n\nfor %%G in (*.sql) do sqlcmd \/S \"%instance%\" \/d \"%dbname%\" -U \"%user%\" -P \"%pass%\" -i\"%%G\" -b -s\"%delimiter%\" -W -o \"%%G%filetype%\"\n\nGOTO End1<\/code><\/pre>\n\n\n\n
or instance2:<\/p>\n\n\n\n
:instance2\n\nfor %%G in (*.sql) do sqlcmd \/S \"%instance2%\" \/d \"%dbname%\" -U \"%user2%\" -P \"%pass2%\" -i\"%%G\" -b -s\"%delimiter%\" -W -o \"%%G%filetype%\"\n\nGOTO End1\n\n\n:End1\n\npause<\/code><\/pre>\n\n\n\n
The script code all together<\/h2>\n\n\n\n
In the end the batch script<\/strong> (sqlcmd_batch_script.cmd<\/em>) should have this form:<\/p>\n\n\n\n
CD \/d \"%~dp0\"\n\n\nfor %%I in (.) do set dbname=%%~nI\nset filetype=.csv\nset delimiter=,\n\n\nset instance=SMATZOURANISLP,1433\nset user=stratos\nset pass=password\n\n\nset instance2=SMATZOURANISLP\\INSTANCE2,1533\nset user2=stratos\nset pass2=password\n\n\n\nIF \"%dbname%\"==\"StackOverflow2013\" GOTO instance1\nIF \"%dbname%\"==\"AdventureWorks2019\" GOTO instance1\n \n\nIF \"%dbname%\"==\"StackOverflow2016\" GOTO instance2\n\n\nGOTO End1\n\n\n:instance1\n\n\nfor %%G in (*.sql) do sqlcmd \/S \"%instance%\" \/d \"%dbname%\" -U \"%user%\" -P \"%pass%\" -i\"%%G\" -b -s\"%delimiter%\" -W -o \"%%G%filetype%\"\n\nGOTO End1\n\n\n \n:instance2\n\nfor %%G in (*.sql) do sqlcmd \/S \"%instance2%\" \/d \"%dbname%\" -U \"%user2%\" -P \"%pass2%\" -i\"%%G\" -b -s\"%delimiter%\" -W -o \"%%G%filetype%\"\n\nGOTO End1\n\n\n:End1\n\npause<\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n