How to export data from SQL Server to Excel

Exporting data from SQL Server to Excel can be achieved in a variety of ways:

• Data Transformation Services [DTS]
• SQL Server Integration Services [SSIS]
• Bulk Copy [BCP]

Rest of above GUI operation there is another option available via the T-SQL language is the OPENROWSET command.This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:

OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
'provider_string' }
, { [ catalog. ] [ schema. ] object
'query'
}
BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
SINGLE_BLOB SINGLE_CLOB SINGLE_NCLOB }
} )
::=
[ , CODEPAGE = { 'ACP' 'OEM' 'RAW' 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]

With the following example you can write a simple job for the operation:-
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO