CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005csv
Trojan.ZBOT
Trojan.ZBOT

Reputation: 1488

Convert result of SQL query into CSV with SQL itself?

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 ?

Upvotes: 0

Views: 6331

Answers (3)

evhen14
evhen14

Reputation: 1927

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

Upvotes: 2

Andrew
Andrew

Reputation: 8703

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.

Upvotes: 0

Anon
Anon

Reputation: 10908

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.

Upvotes: 3

Related Questions