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

SQL keyboard shorcuts for Frequently used queries

If you work extensively with MS SQL SSMS, you would be looking for a way to reuse the queries instead of typing it all over again. Something like a shortcut for the frequently used queries.

I Use the following ways and methods to access the SQL queries that I frequently use

In SSMS
Setting keyboard shortcuts in SSMS.

Tools > Option > Environment > KeyBoard

There are 11 available shortcuts u can set.

To execute, in the query window
for example the table Orders in the DB NorthWind
you need to just write "Orders" in your query window and select the word and hit Ctrl+0 
As you can see in the screenshot above, it will exec the statement
"Select * from Orders" and get you all the records in your DB.


Unfortunately We are not able to add more shortcuts to SSMS. after lil googling , I found this cool tool called Clavier+

where you can store all your frequently used queries

The other Feature of this is that, you can launch any program with a shortcut, also you can use it to launch any file that you frequently open.

Download:
http://utilfr42.free.fr/util/Clavier.php?sLang=en

Note: Suppose you set a shortcut for keys such as Ctrl+C , V then the action that you specify here will take place instead of the OS's Copy Paste function.