将SQL查询数据导出到Excel [英] Export SQL query data to Excel

问题描述

我有一个查询返回一个非常大的数据集。我无法复制并粘贴到我通常做的Excel中。我一直在做一些关于如何直接导出到Excel表的研究。我在运行Microsoft Server 2003的服务器上运行SQL SERVER 2008.我试图使用Microsoft.Jet.OLEDB.4.0数据提供程序和Excel 2007.我已经拼凑了一小段代码，看起来像这样在示例中看到。

  INSERT INTO OPENDATASOURCE（'Microsoft.Jet.OLEDB.4.0'，
'数据Source = C：\Working\Book1.xlsx;扩展属性= EXCEL 12.0; HDR = YES'）
 SELECT productid，price FROM dbo.product 
  

但是这不起作用，我收到一条错误消息说

关键字SELECT附近的语法不正确。

有没有人有任何想法，如何做到这一点，或者可能是一个更好的方法？

解决方案

我不知道这是否正在寻找，但您可以将结果导出为Excel ：

在结果窗格中，单击左上角的单元格突出显示所有记录，然后右键单击左上角的单元格，然后单击将结果另存为。其中一个导出选项是CSV。

您也可以给这个镜头：

 code> INSERT INTO OPENROWSET 
（'Microsoft.Jet.OLEDB.4.0'，
'Excel 8.0; Database = c：\Test.xls;'，'SELECT productid，price FROM dbo .product'）
  

最后，您可以查看使用SSIS（替换DTS）进行数据导出。以下是教程的链接：

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

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

"Incorrect syntax near the keyword 'SELECT'".

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

解决方案

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

这篇关于将SQL查询数据导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！