Export table data to JSON from SQL Server

simmigang 41 Reputation points
2020-11-21T13:57:02.42+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-11-21T14:07:52.737+00:00
    1 person found this answer helpful.
    0 comments No comments

  2. Stefan 1 Reputation point
    2020-11-21T23:46:24.353+00:00

    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.

    0 comments No comments

  3. MelissaMa-MSFT 24,176 Reputation points
    2020-11-23T06:05:38.027+00:00

    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

    0 comments No comments