This post is regarding executing SQL scripts from DOS
batch file by using sqlcmd.
 |
| sqlcmd with batch script |
When you want to setup new environment, you might need to
run lots of database scripts manually. But fortunately we can avoid this manual
step by using sqlcmd command line
utility with the help of DOS batch file.
Create folders and keep SQL scripts inside those folders
and sub folders as
 |
| Scripts in Folder |
Tip: - You should keep your script name start with number so that they will run in same order.
Below is a sample script which execute all scripts inside
folders and its sub folders
@ECHO OFF
SET SQLLOG=%~dp0sql_log.txt
SET SQLSERVER=.\SQLEXPRESS
ECHO.
Starting Script, Server:
%SQLSERVER%,
logging to : %SQLLOG%
ECHO.
CALL:runsql "1 Create
Databases"
CALL:runsql "2 Create
Schema"
CALL:runsql "3 Data\1
Insert"
CALL:runsql "3 Data\2
Insert"
EXIT /B
%ERRORLEVEL%
::Function to
run SQL script files in
a Directory
:runsql
echo.
echo.
Executing Scripts in Directory:
echo.
%~1
echo.
PUSHD %~1
for %%G
in (*.sql) do echo. sqlcmd -S"%SQLSERVER%" -E
-e -i"%%G"
for %%G
in (*.sql) do echo. sqlcmd -S"%SQLSERVER%" -E
-e -i"%%G"
"%SQLLOG%"
for %%G
in (*.sql) do sqlcmd -S"%SQLSERVER%"
-E -e -i"%%G" >>
"%SQLLOG%"
POPD
|
In current script, I am using below options
-S [protocol:]server[instance_name][,port]
-e (echo input)
-E (use trusted connection)
-i input_file
Pushd command and popd command in a batch program is
used to change the current directory from the one in which the batch program
was run and then change it back.
Note: - Your SQL server should be in mixed
mode.
You can refer link to know more about sqlcmd command and
its various available options at https://msdn.microsoft.com/en-us/library/ms162773.aspx
This batch file also log all SQL command that it run in “sql_log.txt”
file.
After running this script, I am able create database,
tables and insert some records in those tables as
 |
| Final DB |
I hope you like this SQL batch trick. Stay tuned for more
articles.
Please leave your comments or share this article if it’s useful for you.
