When it comes to exporting data there are different options available in SQL Server, such as SQL Server Integration Services, Generating Insert Scripts, BCP, Import and Export Wizard and SQLCMD.

When it comes to exporting data as CSV file, BCP and SQLCMD are the easiest methods. Both are command-line tools which can be used to export data.

General syntax for SQLCMD is as follows:

SQLCMD -S <<ServerName>> -E -Q "EXEC AdventureWorks2008R2.dbo.uspExport"
-s "," -o "C:\DataSqlCmd.csv" -h-1

Where, -S specifies Database Server Name,
-E specifies to use Windows Authentication,
-Q specifies the query to be executed against server,
-s specifies column separator character,
-o specifies the destination file and,
-h-1 specifies that column headers should not be exported.

You can provide a SELECT statement or a Stored Procedure to -Q switch. In general I use stored procedures as it allows to do any formatting required which cannot be done using SQLCMD.

I have created the stored procedure dbo.uspExport as below:

USE AdventureWorks2008R2

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 BusinessEntityID,

            JobTitle,

            Gender,

            BirthDate

FROM        HumanResources.Employee

The stored procedure simply returns top5 rows which are to be exported.

Executing the above SQLCMD command will generate the CSV file as follows:

          1,Chief Executive Officer                ,M,      1963-03-02
          2,Vice President of Engineering          ,F,      1965-09-01
          3,Engineering Manager                    ,M,      1968-12-13
          4,Senior Tool Designer                   ,M,      1969-01-23
          5,Design Engineer                        ,F,      1946-10-29

Same data can be exported using BCP as follows:

bcp "EXEC AdventureWorks2008R2.dbo.uspExport" queryout "C:\DataBCP.csv" -c -t , -S (local) -T

Here, -c specifies the character type format,
-t specifies the column separator,
-S specifies the database server name and,
-T specifies to use Windows Authentication.

The output generated by BCP is slightly different than of SQLCMD, output generated by BCP is as follows:

1,Chief Executive Officer,M,1963-03-02
2,Vice President of Engineering,F,1965-09-01
3,Engineering Manager,M,1968-12-13
4,Senior Tool Designer,M,1969-01-23
5,Design Engineer,F,1946-10-29

You can see that there is no padding while exporting using BCP by default, same can be achieved by using -W switch with SQLCMD.

If you need to implement any "complex" formatting, such as using delimiter for starting and end of row or change data format you can do this inside the stored procedure created for exporting data.

USE AdventureWorks2008R2

CREATE PROCEDURE uspExport

AS

SET NOCOUNT ON

SELECT      TOP 5 '#' + CAST(BusinessEntityID AS NVARCHAR(MAX)) + '#' +

            CAST(JobTitle AS NVARCHAR(MAX)) + '#' +

            CAST(Gender AS NVARCHAR(MAX)) + '#' +

            CONVERT(NVARCHAR(20), BirthDate, 107) + '#'

FROM        HumanResources.Employee

Here I have returned result as a single column with specifying ‘#’ as a delimiter and to denote start/end of a row. So all rows returned by stored procedure will be pre-formatted and -s or -t switches of SQLCMD/BCP will have no effect.

#1#Chief Executive Officer#M#Mar 02, 1963#
#2#Vice President of Engineering#F#Sep 01, 1965#
#3#Engineering Manager#M#Dec 13, 1968#
#4#Senior Tool Designer#M#Jan 23, 1969#
#5#Design Engineer#F#Oct 29, 1946#

To see all switches available for BCP/SQLCMD refer BOL links or execute them with -? at command prompt.

Hope This Helps!

Vishal

EMail -> Vishal@SqlAndMe.com
Twitter -> @SqlAndMe
Facebook Page -> SqlAndMe