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.

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

  • Schema changes not updating in Intellisense in SQL 2008 (SSMS)
  • Can you create a CLR UDT to allow for a shared Table type across databases?
  • Why might SQL Server 2008 OLE DB UDL require port 1433 explicitly specified?
  • SQL Server view with a 'select where x is not null' takes ages to complete
  • Can you improve performance with sqlserver views?
  • Hashing web user password in ASP.NET vs SQL CLR
  • “Incorrect syntax near the keyword ‘SELECT'”.

    Does anyone have any ideas about how to do this or possibly a better approach?

  • be careful parts of your document may include personal information that cannot be removed by the Document Inspector
  • How to insert Excel formula to cell in Report Builder 3.0?
  • Alternative to Left Join
  • Importing Excel file with dynamic name into SQL table via SSIS?
  • Insert data into columns of Excel sheet from SQL Server table
  • SQL procedure from Excel run from connection properties with user login as parameter
  • 3 Solutions collect form web for “Export SQL query data to Excel”

    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:

    INSERT INTO OPENROWSET 
       ('Microsoft.Jet.OLEDB.4.0', 
       '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:

    http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm

    If you’re just needing to export to excel, you can use the export data wizard.
    Right click the database, Tasks->Export data.

    I had a similar problem but with a twist – the solutions listed above worked when the resultset was from one query but in my situation, I had multiple individual select queries for which I needed results to be exported to Excel. Below is just an example to illustrate although I could do a name in clause…

    select a,b from Table_A where name = 'x'
    select a,b from Table_A where name = 'y'
    select a,b from Table_A where name = 'z'
    

    The wizard was letting me export the result from one query to excel but not all results from different queries in this case.

    When I researched, I found that we could disable the results to grid and enable results to Text. So, press Ctrl + T, then execute all the statements. This should show the results as a text file in the output window. You can manipulate the text into a tab delimited format for you to import into Excel.

    You could also press Ctrl + Shift + F to export the results to a file – it exports as a .rpt file that can be opened using a text editor and manipulated for excel import.

    Hope this helps any others having a similar issue.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.