This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 22%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi Everyone,
I need to export millions of records from SQL Server to UI so that the user can download either in CSV or excel.
Can we do it using Stored Proc in SQL Server? Please suggest.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 48%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EV%3C/text%3E%3C/svg%3E)
My preferred method is to use FOR JSON AUTO or FOR JSON PATH for this type of requirement. If you need more flexibility in building the structure of JSON PATH would be better option.
You can wrap the code inside stored procedure and execute it too. This way you can parameterize it also if required and run it with your desired combination of parameter values.
You can see some simple examples of how to do it here
whats-new-in-sql-2016-native-json-function-support-generating-json-documents-using-for-json-clause
Hi @simmigang ,
Could you please validate all the answers so far and provide any update?
Best regards
Melissa
If you are on SQL 2016 or later, you can use the FOR JSON clause. See here for details: https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15
If the SELECT is simple, even SQLCMD can do the job saving the records to a CSV directly ... e.g.:
SQLCMD -S "ServerHere" -E -Q "SELECT * FROM TableA WHERE (...)" -s ";" -o "(...)\Output.csv"
More complex queries could be executed from a script-file as well. Moreover, if available, you could use dbmail, also delivering the output-file to the user directly.
Hi @simmigang ,
As mentioned by other experts, you could try with FOR JSON AUTO or FOR JSON PATH.
You could also refer below link which contains several stored procedures that could be used for inserting JSON data into SQL Server, validate the data or to export the data from SQL Server.
SQL Server and JSON: Data import, export and validation
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
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