Executing set of SQL queries using batch file?

batch-file, cmd, database, sql-server

Solution

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.

Problem

Original source