SQL Server stored procedure to export Select Result to CSV
Solution 1
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.
Solution 2
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
Etibar - a tea bar
Software developer in a start-up called Sorry as a Service. Worked as Full-Stack, SQL developer, a software developer in testing and etc.
Updated on June 04, 2022Comments
-
Etibar - a tea bar almost 2 years
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'