SQL Server: Export data to Excel

This article will demonstrate one of the several ways of exporting data from SQL Server 2005 to an Excel spread sheet. This time we will use simple SQL queries to export data unlike the earlier instance when we used SSIS. The precursor to achieving this will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to export data to Excel spreadsheet. It must kept in mind that an Excel template with all the intended columns has to exit before data can be exported and the file must be closed while data is transferred. In the following example data from the “employee” table in the SQL Server 2005 database is being exported to an Excel spreadsheet named “EMP”.

--Export data to Excel
insert into openrowset('Microsoft.Jet.OLEDB.4.0' ,
'Excel 8.0;Database=c:\test\test.xls','Select * from [EMP$]')
select empid, ename from employee

References:
1. Server Settings Configuration Options

---

Possibly related posts: (automatically generated)

• SQL Server: Import data from Excel
• SQL Server: Data dictionary
• SQL: how to
• Entry for August 13, 2008