Exporting Data/Resultset of a Query to CSV

when you need to export the resultset of a query to a csv copying from a resultset window of ssms will give you memory exception when the data is large
Better option will be to use SQLCMD

Also BulkCopy doesnt support queries with multiple lines, but SQLCMD allows you to pass a file containing the query with multiple lines.

Use the below code and paste in notepad and save it as bat file and replace whatever is in red with your parameters.

@ECHO Using SQLCMD
sqlcmd queryout "filepathWithFilename" -c -t , -S DBServerInstance -d databaseName -U username -P password
-i "QueryFileName"
pause

Example:


@ECHO Using SQLCMD
sqlcmd queryout "f:\Output.csv" -c -t , -S ISQL99999q,77777 -d Shipping -U astro -P astrospassword -i "f:\a.txt"
pause
Save it as a bat file

Note: Here the Database name is Shipping 
ISQL99999q,77777 is DB instance Name astro is the username astrospassword is the password


Contents of a.txt file will be the query:
Select * from Shipping.Product  inner Join Shipping.Orders  on Shipping.Orders.ProductId=Shipping.Product.ProductId

Save the a.txt file / the query file and give the same path in the bat file.
 and Run the bat file. 

exporting large data from Database /MS SQL /SSMS to CSV

When there is a need to export lumpsome data from your Database to CSV, the export option wont help you out when you have a custom query or bulk data to export.

You can always use BCP tool for bulk copying.

It is real simple to use , I use it in a batch file to get export several csv's at once.


Use the below code and paste in notepad and save it as bat file and replace whatever is in red with your parameters.

@ECHO Doing Something
bcp "query" queryout "filepathWithFilename" -c -t , -S DBServerInstance -U username -P password

pause

Example:

@ECHO Exporting Orders Table
bcp "Select * from Shipping.Product  inner Join Shipping.Orders  on Shipping.Orders.ProductId=Shipping.Product.ProductId" queryout "d:\Orders.csv" -c -t , -S ISQL99999q,77777  -U astro -P astrospassword

pause


Note: Here the Database name is Shipping and the tables Orders, Product  joined on ProductId
ISQL99999q,77777 is DB instance Name astro is the username astrospassword is the password

Save it as a bat file and Run. 

The output of the file will be the common rows of Table orders and Products saved into D drive with the name Orders.csv


Keep in mind: 
You may see that I have used the database name everywhere ie. on the table names and on join condition. This is the way you are supposed to do it. In case you are having schemas then
DatabaseName.SchemaName.TableName.ColumnName - this should be the format.

Also this doesn't work if your query is large/multiline, for which you can refer the post below.
http://codestruggles.blogspot.in/2015/11/exporting-dataresultset-of-query-to-csv.html