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.
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-1Additional 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
Comments