[MSSQL] Export to Excel using query

http://therebuilds.blogspot.com/2011/11/mssql-export-to-excel-using-query.html

Below example how to export row data directly using queries.

1. Enable Ad Hoc Distributed Queries.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

2. Create new excel sheet and define the columns names exactly the same as the select statements below.

Then
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;', 'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact

Tested on MSSQL 2005.

---

http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/