JBone JBone - 11 months ago 60
SQL Question

Export SQL query data to Excel

I have a query that returns a very large data set. I cannot copy and paste it into Excel which I usually do. I have been doing some research on how to export directly to an Excel sheet. I am running SQL SERVER 2008 on a server running Microsoft Server 2003. I am trying to use the Microsoft.Jet.OLEDB.4.0 data provider and Excel 2007. I've pieced together a small piece of code that looks like this from what I've seen in examples.

'Data Source=C:\Working\Book1.xlsx;Extended Properties=EXCEL 12.0;HDR=YES')
SELECT productid, price FROM dbo.product

However this is not working, I am getting an error message saying "Incorrect syntax near the keyword 'SELECT'". Does anyone have any ideas about how to do this or possibly a better approach?

Answer Source

I don't know if this is what you're looking for, but you can export the results to Excel like this:

In the results pane, click the top-left cell to highlight all the records, and then right-click the top-left cell and click "Save Results As". One of the export options is CSV.

You might give this a shot too:

   'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')

Lastly, you can look into using SSIS (replaced DTS) for data exports. Here is a link to a tutorial: