Executing set of SQL queries using batch file?

Question

I am using a SQL Server database. I have these SQL queries:

Delete from TableA; Delete from TableB; Delete from TableC; Delete from TableD; Delete from TableE; 

Is it possible to run these scripts using a batch file? The database is a remote database.

Thanks!

Answer 1

Save the commands in a .SQL file, ex: ClearTables.sql, say in your C:\temp folder.

Contents of C:\Temp\ClearTables.sql

Delete from TableA; Delete from TableB; Delete from TableC; Delete from TableD; Delete from TableE; 

Then use sqlcmd to execute it as follows. Since you said the database is remote, use the following syntax (after updating for your server and database instance name).

sqlcmd -S <ComputerName>\<InstanceName> -i C:\Temp\ClearTables.sql 

For example, if your remote computer name is SQLSVRBOSTON1 and Database instance name is MyDB1, then the command would be.

sqlcmd -E -S SQLSVRBOSTON1\MyDB1 -i C:\Temp\ClearTables.sql 

Also note that -E specifies default authentication. If you have a user name and password to connect, use -U and -P switches.

You will execute all this by opening a CMD command window.

Using a Batch File.

If you want to save it in a batch file and double-click to run it, do it as follows.

Create, and save the ClearTables.bat like so.

echo off sqlcmd -E -S SQLSVRBOSTON1\MyDB1 -i C:\Temp\ClearTables.sql set /p delExit=Press the ENTER key to exit...: 

Then double-click it to run it. It will execute the commands and wait until you press a key to exit, so you can see the command output.

Answer 2

Check out SQLCMD command line tool that comes with SQL Server. http://technet.microsoft.com/en-us/library/ms162773.aspx