SQL SERVER – Export Data AS CSV from Database Using SQLCMD

We have covered multiple times in this blog that we can import CSV to Database very easily. However, recently I received a very interesting question where the user wants to export data with the help of SQLCMD.

Generically you can use the following syntax:

SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q "Your Query" -s "," -o "C:\Yourfilename.csv"

Method 1: CSV with Column Header

C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,”

Method 2: CSV without Column Header

C:\Users\pinaldave>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,” -h-1

Additional explanation:

• -S”,” – sets the delimiter to the comma
• –w number – sets the length of the CSV line before it wraps
• -W – removes trailing whitespace
• –h-1 – removes header in CSV (Column Header)
• -E (use trusted connection) – instead of this use -U username and -P password