Need to hand a business analyst a CSV from a query every morning. Copy paste from the SSMS grid mangles commas and drops leading zeros. What is the reliable repeatable way to export query results to CSV?
How to export SQL Server query results to a CSV file?
Solved SQL & Databases
MS
Michael Scofield January 15, 2020
2 replies
6,910 views
Reviewed by moderators
Grid copy is unreliable, agreed. Better options:
1
One-off: SSMS, right click results, Save Results As, CSV. Set Tools, Options, Query Results, Text to include column headers first.
2
Repeatable:
bcp or sqlcmd in a scheduled task. sqlcmd -S server -d db -Q "query" -o out.csv -s"," -W runs headless every morning.3
Leading zeros dying means the value is numeric. Cast it to varchar in the query so the zeros survive as text.
sqlcmd in Task Scheduler with a varchar cast on the zip codes. Analyst gets a clean CSV at 6am daily. Solved.