Is there a way to convert results of an SQL query to a CSV file using SQL itself ? I am looking for a solution that works for SQL server 2005 and above. I looked around on google and most of them suggested point-n-click options in management studio, XML based solutions and SQLcmd. I don't want those solutions.
Can we do it in plain SQL ? EDIT - Is it possible to save the results to a CSV file using only SQL ?
Use the BCP tool. It is part of SQL Server. Also, 'csv' is not a well-defined format. You need to think about how you want to handle fields that contain your field and row delimiters (usually comma and newline).
If you really, really need to initiate this from within a SQL Server session, there a few ways to do that. All of them are clunky. Keep in mind that the Windows account that actually runs the SQL Server Service may not even have write permissions to the filesystem where you want to save your csv (it probably shouldn't have permission).
I strongly recommend kicking off this task outside of SQL.
you can use string concatenation but be careful for commas in your result strings
SELECT UserName + ',' + CAST(BirthDate as VARCHAR(12)) + ',' + CAST(NumberOfAccount as VARCHAR(10))
FROM Users
to escape commas use SELECT '"' + ColumnWithCommas + "'" + ... FROM Table
EDIT: You can need to save data to CSV by Copy/Pasting it. As an alternative use this but be careful with permissions. Ideally your account should not have permissions to save to disk
You can use SQLCMD (you should have it if you have SSMS on your machine). Pull up a command prompt. The command will be something along these lines:
sqlcmd -U <userid> -P <password> -S <hostname or ip> -q "<your select statement>" -o "<target file>"
All the double quotes are needed, I think. It works for me with them, so I don't worry about it.
If you want a different separator, add -s "<separator character>"
Depending on the width of each row, you may have to mess around with -w <number of characters>, or your data may start wrapping.
EDIT: Anon makes some very good points about the challenges with CSVs, keep them in mind no matter what approach you take.
