How to Produce an CSV Output File from Stored Procedure in SQL Server

SQL Server stored procedure to export Select Result to CSV

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.

Sqlcmd export data to csv using SQL Server stored procedure

You can add one more parameter to remove the headers from the csv file generated.

Sqlcmd msdn link

-h-1 removes column name headers from the result

-h headers

Specifies the number of rows to print between the column headings. The default is to print headings one time for each set of
query results. This option sets the sqlcmd scripting variable
SQLCMDHEADERS. Use -1 to specify that headers not be printed. Any
value that is not valid causes sqlcmd to generate an error message and
then exit.

For removing the extra padding spaces in the column width, add -W parameter. I have tested it. It is working fine.

-W This option removes trailing spaces from a column. Use this option together with the -s option when preparing data that is to be exported
to another application. Cannot be used with the -y or -Y options.

Export to CSV in stored procedure in SQL Server 2008

You are seeing "Incorrect syntax near queryout". That is a T-SQL error message. You are ,as you said, running this from within a stored procedure. Both BCP and SQLCMD are command line utilities, meaning you will have to run them from a command (DOS) prompt.

There is a way to execute directly from SQL, but you will have to use: xp_cmdshell Here is a good article on how to use it:

https://www.mssqltips.com/sqlservertip/1633/simple-way-to-export-sql-server-data-to-text-files/

This was written for SQL Server 2005 but should work on 2008 as well.

---

Related Topics