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.