Exporting data to excel

Question

I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.

Answer 1

You have the 3 ways posted in the previous answer, or you can do it in plain sql, using OPENROWSET or OPENDATASOURCE.

Some examples:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\TEST.xls;HDR=YES',
'select * from [Sheet1$]');

Both SSIS and SSRS import/export methods can't be done too easily, though they have the advantage of being able to automate their execution, and the copy from SSMS output is only a manual solution. The OPENROWSET/OPENDATASOURCE functions can be called more easily in a procedure or ad-hoc query.

Answer 2

Couple of ways that I know of

• Run procedure in ssms, then copy results into excel (make sure to turn on the option to copy column headers in tools,options,query results)
• Create a SQL server integration services (SSIS) package to run the procedure and write the results to a CDC or excel file.
• If you have SQL server reporting services (SSRS) you can create a report to run the procedure and display the results, then from the report website you can export the report to excel/csv. This can also be scheduled through SSRS subscriptions.