Convert result of SQL query into CSV with SQL itself?

Question

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 ?

score 3 · Accepted Answer · answered Nov 21 '13 at 21:47

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.

Yes, some programming language like VB or C# – Trojan.ZBOT Nov 21 '13 at 22:07 — Trojan.ZBOT, Nov 21 '13 at 22:07

score 2 · Answer 2 · edited May 23 '17 at 12:05

2

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

edited May 23 '17 at 12:05

Community

1
1

answered Nov 21 '13 at 21:01

evhen14

1,839
12
16

Need a plus before the second cast. Can this be saved to a file using SQL itself ? – Trojan.ZBOT Nov 21 '13 at 21:05
Just trying to understand. Are you expecting the output file from SQL Query as CSV ?? – goofyui Nov 21 '13 at 21:09
@goofyui - yes, that is what i am expecting. There is a solution for PDF though - http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/ – Trojan.ZBOT Nov 21 '13 at 21:13

score 0 · Answer 3 · answered Nov 21 '13 at 21:48

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.

Convert result of SQL query into CSV with SQL itself?

3 Answers3