CODEWITHSUNDEEP
sqlsql-server-2008
chrisnz
chrisnz

Reputation: 11

Run large SQL query from a bat file

I am trying to run a monthly extract from a SQL Express DB using a .bat file called from windows scheduler and the SQL query is large (about 50 lines) and I can't work out how to include such a large script in the bat file without it all being flattened out into one (long) single row of text. Is there a carriage return (or similar) command that I can use to keep the query in the readable SQL format like I have below?

SELECT a.[Application_Number]
  ,case when c_MNI >0 then 
    100*(a.monthly_living_allowance +
    a.New_Home_Loan_Repayment_Amount +
    c_mc)/c_MNI 
  else 0 end as nsr_calc
  ,a.[FIN_Total_Net_Service_Ratio] as NSR
  ,a.Total_Annual_Income_Gross_Total as Annual_Gross
  ,a.fin_total_annual_income_net1 as Annual_Net
  ,C_MNI as MNI 
  ,C_MC  as MC
  ,a.[monthly_living_allowance] as MLA
  ,a.[Manual_MLA]
  ,a.[Manual_MLA_Flag] ....etc etc...

Upvotes: 1

Views: 514

Answers (1)

Han
Han

Reputation: 3072

You can use sqlcmd in your bat file. Put your SQL script in a file, let's say it's DoSomeQuery.sql. Then call sqlcmd in your bat file like this:

sqlcmd -S servername -U user -P password -d DB_Name -i DoSomeQuery.sql

If you have multiple sql files, you can use for command in your bat file like this:

for /r . %%f in (*.sql) do sqlcmd -S servername -U user -P password -d DB_Name -i "%%f"

Upvotes: 1

Related Questions