Generating CSV Files - Using BCP

Generating CSV Files - Using BCP

Let's say you need to generate a comma seperated file (CSV) of a list of authors from a stored procedure in SQL Server. There are many ways you can do this, but in this article, we will see how the BCP command-line tool can be used to generate the same. We will also see how to embed this command inside a stored procedure so thay by executing the stored procedure, the CSV file is generated. The following is the example of a stored procedure that can be used for the same: 

            IF (OBJECT_ID ('dbo.GetAuthorsCSV') IS NOT NULL)
            DROP PROCEDURE dbo.GetAuthorsCSV
            GO
            CREATE PROCEDURE dbo.GetAuthorsCSV AS
            BEGIN
            DECLARE @bcpCommand VARCHAR(8000)
            SET @bcpCommand = 'bcp "SELECT au_id, au_fname, au_lname, phone, address FROM pubs..authors" queryout'
            SET @bcpCommand = @bcpCommand + ' C:\Temp\authors.csv -c -t"," -Slp-srinivas -Usa -Ppassword'
            EXEC master..xp_cmdshell @bcpCommand
            END
            
            

 

For the sake of readability, I've split the command into two lines. The -c option tells BCP that we want all data types to be treated as the character datatype. The -t"," specifies the delimiter to use between the various columns. The xp_cmdshell extended stored procedure is used to execute the command. When you execute the stored procedure using EXEC dbo.GetAuthorsCSV, the file is created in the C:\Temp folder. Note that this file will be created in the SQL Server, since the command is executed by a stored procedure that is present in SQL Server.