Search code examples
sql-serverssmssqlcmd

Write query to export database to csv file via Microsoft SQL Server Management Studio

I am trying to write a query that will export the data from a table into a csv file. I cannot use the manual methods of right clicking the results and selecting save as. It has to be via query. I turned on the SQLCMD mode on my Managment Studio and am trying to run this cmd:

sqlcmd -S sqlbusServer -d hhhdb -E -Q "SELECT * FROM Table" 
   -o "MyData.csv" -h-1 -s"," -w 700

in the above code sqlbusServer is the name of the server, and hhhdb is the database name.

I keep getting an error "Incorrect syntax near 'S'. Am I correct in just putting in the server name and db name without any ',", or [ around it?

Solution

  • Try this:

    :!!sqlcmd -S sqlbusServer -d hhhdb -E -Q "SELECT * FROM Table" 
-o "FILEPATH\MyData.csv" -h-1 -s"," -w 700

    I simply added :!! front of "sqlcmd" and a filepath in front of the document name.

    Examples: http://www.codeproject.com/Tips/1017732/SQL-Server-export-to-CSV