Multiple SQL file execution by sqlcmd
August 4, 2012
Hella all!!
Just to start with one SQL requirement i met with @ work..
Requirement: I had to make a Batch script to run my SQL query at the server, which can be very well executed with the query feeded hrough a .sql file and execute the
.sql file through a batch file having a sqlcmd command to execute. But but.. it goes like i had to run multiple .sql files having different query sets in them to make sure i don’t execute all of them together and has to be one after the other which can avoid any cases of SQL blocks occuring in my Server.
Solution: On continuous googling i witnessed different solutions, one saying to run the sql command with a input file to be given as PATH/*.sql, but that runs all the SQL files in the directory which again proves a not right.
And here i got this one good solution multiple selected query files.
Step1: Get all your SQL query files read, as i have done here with 4 files as sql_query1.sql, sql_query2.sql, sql_query3.sql, sql_query4.sql and sql_query5.sql.
Step 2: Get your master SQ file with the follwoing script, i have named it as master_sql.sql
— This is the main caller for each script
SET NOCOUNT ON
GO
:r c:\Scripts\sql_query1.sql
:r c:\Scripts\sql_query2.sql
:r c:\Scripts\sql_query3.sql
:r c:\Scripts\sql_query4.sql
:r c:\Scripts\sql_query5.sql
PRINT ‘DATABASE CREATE IS COMPLETE’
GO
Step 3: And the finally all you got to do is get your batch file ready to call the master sql file, i have named this as call_master.sql
set Server_Name=sqlserver_name
set Database_Name=Advetureworks
sqlcmd -s %Server_Name% -d %Database_Name% -e -w -s, -b -i c:\Scripts\sql_query5.sql > c:\Scripts\output.txt
Step4: Just execute your .bat file and see the query working smooth and swift 🙂
Share and let others know because sharing is good.