SQL Server stored procedure to export Select Result to CSV

Question

In my stored procedure, I want to export select result to a .CSV file. I need to write a stored procedure which selects some data from different tables and saves it to a .CSV file.

Selecting part is ready

 SELECT DISTINCT 
                  PER.NREGNUMBER_PERNUM AS [Registration Number], 
                  PER.CFAMNAME_PER AS [Family Name], 
                  PER.CGIVNAME_PER AS [Given Name], 
                  CONVERT(varchar(10), CONVERT(date, PER.DBIRTHDATE_PER, 106), 103) AS [Birth Date], 
                  PER.CGENDER_PERGEN as [Gender],
                  PHONE.MOBILE_NUMBER
        FROM      PERSON AS PER 
                  LEFT OUTER JOIN
                  PHONE ON PER.NREGNUMBER_PERNUM = PHONE.NREGNUMBER_PPHPER AND PHONE.CPRIMARY_PPH = 'Y'

All you've shown us is some pretty standard error handling and a `SELECT GETDATE()`. We have no idea of what your table structure looks like, what your data looks like, nor what data you actually want to have in the CSV. What possible help do you expect us to be able to give you at this point? — Damien_The_Unbeliever, Nov 06 '14 at 08:00
because of privacy I didn't share the procedure. Let's say in this procedure we need to save current time in CSV file. Anything saves the result of this procedure will be useful to us. Thanks — Etibar - a tea bar, Nov 06 '14 at 08:04
Just change the field/table names to something inconspicuous — Matt, Nov 06 '14 at 08:34
I changed it. But does the number or the name of columns matter? — Etibar - a tea bar, Nov 06 '14 at 09:22

score 2 · Accepted Answer · answered Nov 07 '14 at 12:29

The task was I had to export from database some data to .CSV at specified time. In the begining we wanted to use windows scheduler for running stp. The STP had to be able to export data. But I couldn't find a way. Instead the thing what we did was creating simple STP which brings only data . And we created batch file which calls STP and export result to .CSV file. The batch file is simple

sqlcmd -S Etibar-PC\SQLEXPRESS -d MEV_WORK -E -Q "dbo.SelectPeople" -o "MyData1.csv" -h-1 -s"," -w 700

dbo.SelectPeople is STP
Etibar-PC\SQLEXPRESS is Schema
MEV_WORK is Database name.

score 1 · Answer 2 · edited Jan 15 '17 at 12:23

i have build a procedure to help you all

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- example exec Sys_Database_exportToCsv 'select MyEmail from 
QPCRM.dbo.Myemails','D:\test\exported.csv'

create PROCEDURE Sys_Database_exportToCsv

(
@ViewName nvarchar(50),
@exportFile nvarchar(50)
)

AS

BEGIN

SET NOCOUNT ON;

EXEC sp_configure 'show advanced options', 1;  

RECONFIGURE; 

EXEC sp_configure 'xp_cmdshell', 1;  

RECONFIGURE; 

Declare @SQL nvarchar(4000)

Set @SQL = 'Select * from ' + 'QPCRM.dbo.Myemails'

Declare @cmd nvarchar(4000)

SET @cmd = 'bcp '+CHAR(34)+@ViewName+CHAR(34)+' queryout 
'+CHAR(34)+@exportFile+CHAR(34)+' -S '+@@servername+' -c -t'+CHAR(34)+','+CHAR(34)+' -r'+CHAR(34)+'\n'+CHAR(34)+' -T'

exec master..xp_cmdshell @cmd

EXEC sp_configure 'xp_cmdshell', 0;  

RECONFIGURE; 

EXEC sp_configure 'show advanced options', 0;  

RECONFIGURE; 

END

GO

score 0 · Answer 3 · answered Nov 06 '14 at 08:27

0

To create a CSV string from you table result follow the instruction on

http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

As far as I know there is no way to save data into a file on disk directly using TSQL. To save a file, you can create a SQL-CLR stored procedure instead of a TSQl one in which you can use regular C# code.

answered Nov 06 '14 at 08:27

Farzan Hajian

1,799
17
27

I amn't allowed to use C#.Are you sure that it isn't possible using TSQL? – Etibar - a tea bar Nov 06 '14 at 09:12
I've not seen any example about writing into a file in TSQL. But it seems sqlcmd can do what you need (http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd) and if you have the permission to execute xp_cmdshell, you can call sqlcmd directly from your TSQL. – Farzan Hajian Nov 06 '14 at 12:50
This another link that might be interesting http://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008 – Farzan Hajian Nov 06 '14 at 12:58

SQL Server stored procedure to export Select Result to CSV

3 Answers3

Linked