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