tiredBuffalo of CommandLine
11/1/2019 - 8:21 PM

Command Line Utility for SQL Script and CSV Export Automation

sqlcmd is a command line utility used to automate sql statements and scripts.

The code below will find the location of your stored sql query, run it then export a csv to your designated folder location.

Note that -h-1 will remove column headers. To add them back in, simply add an additional select statement with the columns you would like to have as your headers.

For full automation, save your command line code as a .bat file then set it up for automation through the task scheduler.

sqlcmd
content_copyfile_download
#Basic framework
sqlcmd -S <server name> -i <folder location of your sql file>\you_query.sql ^
      -o "report.csv" ^ 
      -h-1 -W -s"," -w 700
      
#Actual example
sqlcmd -S lab -i \\appshare.echo.com\Strategic_Analytics\Ben\CBP_analysis\Alerts\V1.sql ^
    -o "\\appshare.echo.com\Strategic_Analytics\Ben\CBP_analysis\Alerts\RepCustAlerts.csv" ^
    -h-1 -W -s"," -w 700
    
Descriptions of the switches being used: 

-S server name 
-i input_file
-o output_file
-h-1 removes the header and from the result
-W removes trailing spaces from each individual field
-s"," sets the column separator to the comma (,)
-w 700 sets the row width to 700 characters. This will prevent text wrapping

Example for adding headers back to your file:

SELECT 
  'BillingRep, CustomerID, CustomerName,'
SELECT
  *
FROM
  db