Exporting data from SQL Server to Excel

note: in SSMS there is a wizard which creates an SSIS package for you, to migrate data.
However having a simple SQL script is easier to maintain + gives you more control.




declare @pathToExcelDoc varchar(512)
SELECT @pathToExcelDoc = 'D:\testExportToExcel.xls'


--create an EXCEL file and export data to it
--creates a nicely formatted Excel workbook, which can be later imported into SQL Server (after you edit out the title rows at the top of the sheet)
EXEC sp_makewebtask
@outputfile = @pathToExcelDoc,
@query = '
SELECT TOP 10 *
FROM
tblLicence_LING
',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'


SQL executing the command line bcp:


--note: this will overwrite an existing file!
Exec Master..xp_cmdshell 'bcp "Select top 5 * from  eLicensing_WEB.dbo.tblLicence_LING" queryout "d:\testing.xls" -c -S"SERVER\SQLSERVER_2005" -T'


references:

• http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

• http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/