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!
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.
.BAT file. You might want to put a pause at the next line so that the CMD window doesn't run the delete and exit, and you are able to see the output before closing the window. I updated my answer for this.
Check out SQLCMD command line tool that comes with SQL Server. http://technet.microsoft.com/en-us/library/ms162773.aspx
-S parameter to choose the serverUse the SQLCMD utility.
http://technet.microsoft.com/en-us/library/ms162773.aspx
There is a connect statement that allows you to swing from database server A to server B in the same batch.
:Connect server_name[\instance_name] [-l timeout] [-U user_name [-P password]] Connects to an instance of SQL Server. Also closes the current connection.
On the other hand, if you are familiar with PowerShell, you can programmatic do the same.
http://technet.microsoft.com/en-us/library/cc281954(v=sql.105).aspx
Different ways:
Using SQL Server Agent (If local instance)
schedule a job in sql server agent with a new step having type as "T-SQL" then run the job.
Using SQLCMD
To use SQLCMD refer http://technet.microsoft.com/en-us/library/ms162773.aspx
Using SQLPS
To use SQLPS refer http://technet.microsoft.com/en-us/library/cc280450.aspx
sqlcmd /?