How do I create a batch file, that runs SQL?

Question

I have no knowledge of Batch file commands, but I need to use my SQL to run reports in a Batch file. Can anyone help me?

Answer 1

You use stored procedures to run multiple SQL commands.

MySQL:

http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

Transact-SQL (Microsoft):

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=14&rl=1

Answer 2

You will have to use a command line interface from whatever DBMS you are using if it has one. (I gave a link to Microsoft's documentation on MS Access as a starting point.)

A batch file is easy to make. Just open up notepad and type this:

@echo off
echo "hello [your name] - these are your files:"
dir /w

save this file as "FirstBatch.bat" and then run it. You will run the file that will display a message and then list the contents of the directory.

From there, you can do anything that supports command line arguments. Try searching for some tutorials.

Answer 3

Nice intro to making a .bat file. but doesn't answer the question, you can use the isql (or if you're using sql 2005 osql) program to log onto with credentials and run either a script or sql you type in your batch file. if you're not using Ms sql server, search for command line equivalents for you sql server. sorry.

Answer 4

All you want to include is SQLServer motive force, inspite of the indisputable fact that it truly is continually sensible to have community DB for sorting out your software. there is loose dev version of sq. Server (convey?) that you'll receive and set up on your community computing gadget

Answer 5

I do a lot of these on a Sun box with UNIX

An example, stored in a file that is executed

sqlplus -s username/password@schema
set linesize 6000
set feedback off
set heading off
set termout off
set trimspool on
set pagesize 100
set echo off
spool c:\oracle\ora92\bin\ctbnd.sql
select 'define vsysdate = ',
to_char(sysdate, 'yyyy-mm-dd-hh24:mi:ss') from dual;
spool off
@c:\oracle\ora92\bin\ctbnd
spool c:\oracle\ora92\bin\ctbnc.sql
select 'define vsystitle = ',
to_char(sysdate, 'yyyymmddhh24mi') from dual;
spool off
@c:\oracle\ora92\bin\ctbnc
spool c:\oracle\ora92\bin\ctbn&vsystitle..csv

SELECT 'H'||',' "h,", 0||',' "0,",
ch_header_seqid||',' "hseq,",
ch_report_datetime||',' "Date,",
ch_study_id||',' "Study #,",
ch_brief_title||',' "Brief title,",
ch_mrn||',' "MRN#,",
ch_pt_lname||',' "Patient last name,",
ch_pt_fname||',' "Patient first name,",
ch_pt_minitial||',' "Init,",
ch_last4_ss||',' "SSN,",
ch_dob||',' "DOB,",
ct_type_id||',' "Type,",
ch_pi_lname||',' "PI last name,",
ch_pi_fname||',' "PI first name,",
ch_pi_minitial||',' "Pinit,",
ch_pi_phone||',' "PI phone,",
ch_pi_phone_ext||',' "PI ext,",
ch_pi_fax||',' "PI fax,",
ch_pi_address||',' "PI address,",
ch_pi_email||',' "PI email,",
ch_rc_lname||',' "Coord last name,",
ch_rc_fname||',' "Coord first name,",
ch_rc_minitial||',' "Cinit,",
ch_rc_phone||',' "Coord phone,",
ch_rc_phone_ext||',' "Coord ext,",
ch_rc_fax||',' "Coord fax,",
ch_rc_address||',' "Coord address,",
ch_rc_email||',' "Coord email,",
ch_fc_lname||',' "Fin last name,",
ch_fc_fname||',' "Fin first name,",
ch_fc_minitial||',' "Finit,",
ch_fc_phone||',' "Fin phone,",
ch_fc_phone_ext||',' "Fin ext,",
ch_fc_fax||',' "Fin fax,",
ch_fc_address||',' "Fin address,",
ch_fc_email||',' "Fin email,",
ch_revision_of||',' "Revision,",
ch_fin_number||',' "Fin number,",
ch_worked_by||',' "Worked by,",
ch_worked_datetime||',' "Worked on,",
ch_comment||',' "Comment,",
ch_created_datetime||',' "Create date,"
FROMsst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
ANDct_type_id = 'MSO'
ORDER BY ch_header_seqid;

SELECT 'D'||',' "d,",
p.cp_display_order||',' "Order,",
d.ch_header_seqid||',' "dseq,",
to_char(d.cd_visit_datetime, 'mm/dd/yyyy hh24:mi:ss')||',' "Visit date,",
d.cd_activity_name||',' "Activity name,",
translate(d.cd_procedure_code, ',', '.')||',' "Proc code,",
d.cd_activity_group||',' "Activity group,",
d.cd_activity_pool||',' "Activity pool,",
translate(d.cd_procedure_desc, ',', '.')||',' "Proc desc,",
d.cd_budget_amount||',' "Budget amount,",
d.cd_fee_schedule_amount||',' "Fee schedule,",
d.cp_payor_id||',' "Payor,"
FROMsst.ctbn_detail d,
sst.ctbn_header h,
sst.ctbn_payor p
WHEREp.cp_payor_id= d.cp_payor_id
ANDd.ch_header_seqid= h.ch_header_seqid
ANDh.ch_created_datetime > (SELECT CE_LASTRUN_DATETIME FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
ANDh.ct_type_id = 'MSO'
ORDER BY d.ch_header_seqid, p.cp_display_order, d.cd_item_id;

UPDATE sst.ctbn_extract
SETCE_LASTRUN_DATETIME = to_date('&vsysdate', 'yyyy-mm-dd-hh24:mi:ss')
WHERE ce_jobname = 'EXTRACT';

spool off
quit

This runs and creates spool files that can be used in subsequent jobs.