Stored Procedure-export results to CSV file

Question

Is there a way inside of a TSQL stored procedure to export the results to a CSV file?

Thank you.

Answer 1

Good day

Using Transact SQL We execute scripts which meant to direct the database side but SQL Server provides multiple option to execute scripts in other languages from the transaction. Here are several options you can use:

(1) Execute command shell command using the built it procedure xp_cmdshell - this is probably the simplest solution for most users

(2) Execute Don.Net code using SQLCLR and build the file in the .Net code

(3) Execute Python script and create the CSV

(4) Same as before Execute R script

(5) Trigger external server like sending email and in the server you create event to build the CSV

(6) You can call a method of an OLE object using sp_OAMethod

(7) You can use OPENROWSET and INSERT the result of the query to CSV file using the Microsoft.ACE.OLEDB.12.0 provider

(8) and there are many many many more options which can be done, if someone really want to play with the options...

NOTE!!! Remember the sentence "when you have a hammer, all you see is nails". The fact that you can do something does not mean you should do it !!!

For most cases all the above is not recommended. In order to find the solution which best fits your needs you need to take step back and think about what you really need and not what you want to do now. Maybe with better design this requirement will not even be needed.

Remember that SQL Server is only a set of services and in order to execute something you use client, so maybe ity is better to create the CSV from the client directly?

Answer 2

In one single word: No.

That said, I am sure that will be people who will suggest using xp_cmdshell to invoke BCP, but then you are outside SQL Server. And xp_cmdshell comes with security considerations.

It is much better to orchestrate this from the outside. There are plenty of options, all depending your needs. BCP, that I mentioned, can easily produce CSV files, if you don't need values to be quoted. If you need values to be quoted, it still doable, you only need a format file. But if you need values to be quoted only when needed BCP is out.

Answer 3

Hi @Michael Henderson ,

I found a similar problem, please refer to：
How to produce an csv output file from stored procedure in SQL Server

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table