SQL Statements in a Windows Batch File

Question

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

The RDBMS and version that you are using will probably make a big difference here. — Tom H, Dec 23 '08 at 19:46
Also, if you enable delayed expansion , the batch file will be able to send queries that have greater than and less than symbols in them. The point is that you can script ANY SQL query using a batch file without an external script. I do it all the time. Also, you can do the same with "sqlcmd.exe" or "osql.exe" as you can do with "mysql.exe". — djangofan, Nov 30 '11 at 19:56

score 3 · Accepted Answer · answered Dec 23 '08 at 19:39

3

For a single command you can use this trick:

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

answered Dec 23 '08 at 19:39

Dave Costa

47,262
8
56
72

score 3 · Answer 2 · answered Dec 23 '08 at 19:45

3

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

answered Dec 23 '08 at 19:45

Mehrdad Afshari

414,610
91
852
789

score 2 · Answer 3 · answered Dec 23 '08 at 19:41

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

score 1 · Answer 4 · answered Dec 23 '08 at 19:51

1

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

answered Dec 23 '08 at 19:51

Thuglife

394
2
3

score 0 · Answer 5 · answered Dec 23 '08 at 19:52

0

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.

answered Dec 23 '08 at 19:52

Diomidis Spinellis

18,734
5
61
83

score 0 · Answer 6 · answered Sep 10 '11 at 07:48

0

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

answered Sep 10 '11 at 07:48

Andy Joiner

5,932
3
45
72

djangofan · Answer 7 · 2011-11-30T20:11:48.913

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

SQL Statements in a Windows Batch File

7 Answers7

Linked