none
Stored Procedure Result to CSV File RRS feed

  • Question

  • How to write the Stored Procedure Result to CSV File.

    I know the way to write the Query Result to CSV

     

    but how to write the stored procedure result to CSV

    I hope you all under stood my question.

     

    Thanks all in advance.

    Wednesday, April 21, 2010 12:47 PM

Answers

  • You can use bcp to export stored procedure results to flat file. Demo follows:

    bcp "exec sp_who" queryout f:
    \data\export\sp_who.txt -c -t, -T -S YOURSERVER

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Wednesday, April 21, 2010 1:21 PM
    Moderator
  • Hi lokesh1986,

     See the below example script, for storing results of a stored procedure into a table and then exporting it into a csv file:

     

    if OBJECT_ID('sproc1') is not null
    drop procedure sproc1
    go
    
    create procedure sproc1
    as
    begin
    	select 1 as c1,'xx' as c2
    	union
    	select 2 as c1,'xx' as c2
    	union
    	select 3 as c1,'xx' as c2
    	union
    	select 4 as c1,'xx' as c2
    	union
    	select 5 as c1,'xx' as c2
    	union
    	select 6 as c1,'xx' as c2
    	union
    	select 7 as c1,'xx' as c2
    end
    go
    
    create table csvTable (c1 int, c2 varchar(50))
    
    insert into csvTable exec sproc1
    exec xp_cmdshell 'bcp "select c1,c2 from dbname..csvTable" queryout "<path>\result.csv" -T -c -t","'
    
    drop table csvTable
    

    Kiran (www.ggktech.com)
    Wednesday, April 21, 2010 1:19 PM

All replies

  • Hiii Lokesh,

    There is no Straight forward Function available to do this from the Stored Procedure.

    You can create the CSV Files from the DTS or SSIS. Try converting your code in SSIS or DTS.

    Thanks.


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Wednesday, April 21, 2010 12:50 PM
  • can you tell me first of all are you aware of DTS or SSIS facility?

    if yes then you can do that easily in SSIS using controls like -

    take one DataFlowTask in SSIS Package and in DataFlowTask use OLE DB Source  give database connection & this one execute your stored procedure next take Flat File Destination set file format & this give you CSV file as a output.

    let me know if you want another solution without using DTS or SSIS.

    Thanks,

     


    Kapil Khalas
    Wednesday, April 21, 2010 1:12 PM
  • Hi lokesh1986,

     See the below example script, for storing results of a stored procedure into a table and then exporting it into a csv file:

     

    if OBJECT_ID('sproc1') is not null
    drop procedure sproc1
    go
    
    create procedure sproc1
    as
    begin
    	select 1 as c1,'xx' as c2
    	union
    	select 2 as c1,'xx' as c2
    	union
    	select 3 as c1,'xx' as c2
    	union
    	select 4 as c1,'xx' as c2
    	union
    	select 5 as c1,'xx' as c2
    	union
    	select 6 as c1,'xx' as c2
    	union
    	select 7 as c1,'xx' as c2
    end
    go
    
    create table csvTable (c1 int, c2 varchar(50))
    
    insert into csvTable exec sproc1
    exec xp_cmdshell 'bcp "select c1,c2 from dbname..csvTable" queryout "<path>\result.csv" -T -c -t","'
    
    drop table csvTable
    

    Kiran (www.ggktech.com)
    Wednesday, April 21, 2010 1:19 PM
  • You can use bcp to export stored procedure results to flat file. Demo follows:

    bcp "exec sp_who" queryout f:
    \data\export\sp_who.txt -c -t, -T -S YOURSERVER

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
    Wednesday, April 21, 2010 1:21 PM
    Moderator
  • Hello Kapil

    I need that solution that you give in SSIS, but i am not very familiar with DTS or SSIS;

    Could you please explain step by step that solution.

     

    Thanks in advance!

    Wednesday, May 4, 2011 5:14 PM
  • DECLARE @cmd nvarchar(4000)
    SET @cmd = 'bcp dbname.dbo.TableName out D:\ResultFile.csv -c -t, -T -S'+@@SERVERNAME
    EXEC master.dbo.xp_cmdshell @cmd

    If you want to store it in a separate folder , create the folder first and then assign that folder path in the query. 

    Sunday, May 7, 2017 7:53 AM