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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt ComputerName gt   lt InstanceName gt  -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

[sql-server] Executing set of SQL queries using batch file?

Examples related to sql-server

Examples related to database

Examples related to batch-file

Examples related to cmd