Surendra Sharma

Surendra Sharma

Search This Blog

Friday, November 4, 2016

Run SQL scripts from DOS batch file



This post is regarding executing SQL scripts from DOS batch file by using sqlcmd.

sqlcmd with batch script
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
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
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.

No comments:

Post a Comment