CODEWITHSUNDEEP
sqlbatch-file
Ryan Thames
Ryan Thames

Reputation: 3204

SQL Statements in a Windows Batch File

Is there any way to have a Windows batch file directly input SQL statements without calling a script? I want the batch file to login to SQL and then enter in the statements directly.

EDIT: I'm using Oracle v10g

Upvotes: 2

Views: 9982

Answers (7)

djangofan
djangofan

Reputation: 29689

Here is a rough example script for MSSQL which may be able to be modified for Oracle:

@ECHO off
SETLOCAL ENABLEDELAYEDEXPANSION
:: batch file for sql query
SET STARTDATE=20101010
SET ENDDATE=20111109
SET AGENCYNAME=Agency
SET DBNAME=AccidentDB

SET SQLSTRING=SELECT Acc.INC_ID,^
 Veh.MAKE, Veh.MODEL, Veh.LIC_NUM^
 FROM Acc,^
 lnk_Acc_Veh, Veh^
 WHERE     (INC_NUM LIKE '20115000%')^
 AND lnk_Acc_Veh.link_id=Veh.key^
 AND lnk_Acc_Veh.link_id=Acc.key^
 AND Acc.date ^> '%STARTDATE%' OR Acc.date ^< '%ENDDATE%';

CLS
@ECHO.
@ECHO.
@ECHO DBNAME is %DBNAME%
@ECHO.
@ECHO SQLSTRING is "!SQLSTRING!"
@ECHO.
@ECHO ------------------------------------------------------
@sqlcmd.exe -b -S localhost -E -d !DBNAME! -Q "!SQLSTRING!" -W
@ECHO.
@ECHO Report is done. Hit any key to close this window....
@pause>nul

Upvotes: 0

Andy Joiner
Andy Joiner

Reputation: 6581

You can use http://tekkies.co.uk/go/runsqloledb

e.g. RunSQLOLEDB "Provider=SQLOLEDB;Data Source=(local);..." "SELECT GetDate()" or RunSQLOLEDB @ConnectionString.txt @Query.sql

Upvotes: 0

Diomidis Spinellis
Diomidis Spinellis

Reputation: 19385

The odbc command of the outwit tool suite allows you to run select statements on any database for which an appropriate ODBC data source has been defined.

Upvotes: 0

Thuglife
Thuglife

Reputation: 404

You could use sqlcmd (for sql server) or System.Data.Odbc.OdbcCommand from powershell.

Upvotes: 1

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422252

To run something on SQL server 2005/2008, you could use sqlcmd command line utility. sqlcmd -h prints the list of switches.

Upvotes: 3

Bevan
Bevan

Reputation: 44327

Short answer: No. Batch files by themselves can't do this.

Long answer: You may be able to come close, depending on which kind of database server you're using, and what the capabilities the commandline client provides.

What kind of database server are you using? Oracle, mySql, Sybase, Microsoft, Terradata, ???

For example, with a Sybase database, you can use the isql commandline client to run from a batch file:

isql -S server -D database -U user -P password -i script

Upvotes: 2

Dave Costa
Dave Costa

Reputation: 48131

For a single command you can use this trick:

echo select * from dual; | sqlplus user/pw@db

Upvotes: 3

Related Questions