Click here to Skip to main content
13,201,050 members (52,235 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi All

I have an Sql Stored Procedure and i want to export data to the .csv file from my Stored Procedure. my procedure is --



create Proc ExportTest
AS
BEGIN
SELECT * FROM EMPLOYEE
 
-- I NEED TO EXPORT RESULT OF THIS SELECT STATEMENT TO THE .CSV FILE
END



Thanks
Posted 15-Feb-13 1:23am
Updated 12-Jul-17 14:00pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

If you can use a command line, I suggest you check the bcp utility[^]. It is a powerful command line tool that allows exporting (or importing) data in various formats.

Here[^] you can read a nice article on how to use the utility with a few examples.

If you have troubles with it, just comment below. We will be able to assist you.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

you can use query Like given below



declare @sql varchar(8000)
select @sql = 'bcp "select * from EmailVarification..tblTransaction" queryout c:\bcp\Tom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql



in this query EmailVarification is DB name and tblTransaction is table name.
for execute this query you have to set path for write file.
currently in this query you have to create a "bcp" folder in "C:\ "Drive .

Thanks
  Permalink  
Comments
Member 12055056 13-Oct-15 7:56am
   
Can I for destination folder put remote location (remote server)?

Thanks,

Ivana
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

Yes, it is possible to do it from a stored procedure. We currently have something like this working in our production environment.

Use T-SQL

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName


But, there's a couple of caveats:

1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.

2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Sorry, it is not possible to write data to disk using Stored Procedure. You can use SQL Server Export Import Wizard to generate the file and save it as a SSIS package for further use.
  Permalink  
Comments
Christian Graus 22-Jan-14 17:19pm
   
Nope, this is wrong.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171020.1 | Last Updated 12 Jul 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100