ramlicious Blogs by Tina & Prabhu

August 12, 2010

Exporting Recordsets to an Excel using OPENROWSET

Filed under: SQL Server — Prabhuram @ 2:46 am

To me one easiest way to export data from SQL Server to an excel spreadsheet is by using the OPENROWSET command. All you do is:

  • Create an excel file(Nwind.xls (13.50 kb)), that has the column names of the query result
  • And run the OPENROWSET query to import data

Here is a sample SQL Query that uses Northwind DB, which uses the excel file in E:\:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\NWind.xls;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM [Customers]

The entire script can be automated for daily tasks using a batch file by reusing the same excel file.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress