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
1. Server Settings Configuration Options
Subscribe to comments with RSS.