CODEWITHSUNDEEP
sql-server-2008
Blade3
Blade3

Reputation: 4360

Executing a SQL Server Script from a batch file

I have a script that I need to execute using a batch file. Do I use SQLCMD in the batch file to run the script? Also, the script inserts data to a table in a database. How should I format the SQLCMD in the batch file so it knows what database it is suppose to work with?

Upvotes: 2

Views: 38575

Answers (3)

Krishan Kumar Gorav
Krishan Kumar Gorav

Reputation: 57

If you want to execute all .sql files (multiple sql scripts in a folder) for multiple database then create a batch file "RunScript-All.bat" with below content

echo "======Start - Running scripts for master database======="
Call RunScript-master.bat
echo "=======End - Running scripts for master database=========="
pause
echo "=====Start - Running scripts for model database========"
Call RunScript-model.bat
echo "=======End - Running scripts for master database=========="
pause

Definition for individual batch file for a specific database i.e. "RunScript-master.bat" can be written as per below

for %%G in (*.sql) do sqlcmd /S .\SQL2014 /U sa /P XXXXXXXXX /d master  -i"%%G"
::pause

Create many files for different databases and call them from "RunScript-All.bat".

Now you will be all to run all sql scripts in many database by clicking on "RunScript-All.bat" batch file.

Upvotes: 1

nageeb
nageeb

Reputation: 2042

First, save your query into an sql text file (text file with .sql extension). Make sure to add the USE statement at the beginning, which tells the server which database you want to work with. Using the example from MSDN:

USE AdventureWorks2008R2;
GO
SELECT p.FirstName + ' ' + p.LastName AS 'Employee Name',
a.AddressLine1, a.AddressLine2 , a.City, a.PostalCode 
FROM Person.Person AS p 
   INNER JOIN HumanResources.Employee AS e 
        ON p.BusinessEntityID = e.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress bea 
        ON bea.BusinessEntityID = e.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = bea.AddressID;
GO

Then in your batch file, you run SQLCMD and pass it the sql file (with path) as a parameter.

sqlcmd -S myServer\instanceName -i C:\myScript.sql

If you need to authenticate as well, you'll need to add in -U and -P parameters to your SQLCMD command.

Here's an MSDN article dealing with the sqlcmd utility with more details.

Upvotes: 8

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

Use the -S switch to specify server and instance names, e.g. -S MyDbServer\Database1

SQLCMD documentation found here.

Upvotes: 3

Related Questions