My query returns a huge amount of data. I normally copy and paste it into Excel, but I cannot. I have been looking for the best ways to export straight to an Excel sheet. On a machine running Microsoft Server 2003, I have SQL SERVER 2008 installed. I'm attempting to use Excel 2007 with Microsoft.Jet.OLEDB.4.0 data provider. From samples I've seen, I've pulled together a short piece of code that appears like this.
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'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?