32

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user755806
  • 6,565
  • 27
  • 106
  • 153

4 Answers4

66

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.

Shiva
  • 20,575
  • 14
  • 82
  • 112
  • +1 The question says "The database is remote database."... can you adjust the command line? – Andomar Feb 04 '14 at 13:43
  • Shiva, Thanks much for your answer. Can i place -E -S SQLSVRBOSTON1\MyDB1 -i C:\Temp\ClearTables.sql in batch file and just double click on it ? – user755806 Feb 04 '14 at 13:49
  • Yes. Save it as a `.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. – Shiva Feb 04 '14 at 13:52
  • This will help http://www.mssqltips.com/sqlservertip/2478/connecting-to-sql-server-using-sqlcmd-utility/ – Punter015 Feb 04 '14 at 13:53
  • Shiva, can i use same sqlcmd to run the queries against oracle db? – user755806 Feb 04 '14 at 13:57
  • Oracle's command line utility is called `sqlplus` not `sqlcmd`. So for oracle, you will have to have something like the following content in the batch file. You have to tweak this, but this will get you started `@echo off sqlplus -s -l user/password@MyDb @C:\Temp\ClearTables.sql > DeleteTable.log` – Shiva Feb 04 '14 at 14:00
  • While this solution will work, you have check error codes if you want this to be a job. Otherwise, how do you know it completed. See article for more - http://www.computerhope.com/forum/index.php?topic=91064.0 – CRAFTY DBA Feb 05 '14 at 13:56
  • how to do it in local database, i used : `sqlcmd -E -S localhost\sqlexpress -i C:\test.sql -o C:\TestResults.txt` in script i am creating table i have `USE [TestDB] GO`, but generated `txt`file i get this error:`Changed database context to 'TestDB'.` – Shaiju T Dec 03 '15 at 14:47
5

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

BateTech
  • 5,780
  • 3
  • 20
  • 31
  • Bate Tech, i have to run the batch file in the machine in which no sql server is installed. I have to run it on remote database. Database is not there in the machine in which i am executing the batch file. – user755806 Feb 04 '14 at 13:42
  • Use the :connect statement. This also can be done in SSMS using the SQLCMD window type. – CRAFTY DBA Feb 04 '14 at 13:43
  • @user755806: You can use sqlcmd's `-S` parameter to choose the server – Andomar Feb 04 '14 at 13:43
  • Use powershell below. It is installed on every Windows 7 machine and above or install the SSMS management pack. – CRAFTY DBA Feb 04 '14 at 13:43
  • @user755806 I updated my answer for remote database. Check it out. – Shiva Feb 04 '14 at 13:48
  • You can install SQLCMD without having SQL Server installed. http://stackoverflow.com/questions/18872387/how-to-run-sqlcmd-remotely-on-a-machine-which-does-not-have-ssms – BateTech Feb 04 '14 at 13:49
2

Use 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

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
0

Different ways:

  1. 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.

  2. Using SQLCMD
    To use SQLCMD refer http://technet.microsoft.com/en-us/library/ms162773.aspx

  3. Using SQLPS
    To use SQLPS refer http://technet.microsoft.com/en-us/library/cc280450.aspx

Punter015
  • 1,718
  • 10
  • 13
  • Using SQL Server Agent will not help. The database is remote. You will need a linked server in addition. – CRAFTY DBA Feb 04 '14 at 13:55
  • @CRAFTYDBA That's correct (will update the answer). but a possible way to run scripts in local instance. – Punter015 Feb 04 '14 at 13:57
  • Looks like the SQLPS link is being deprecated and redirects to the new version of SQLPS Module https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15 – Kris Kilton Feb 05 '20 at 16:27