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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: