Export data from SQL Server to Excel

Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005).

For some reason if at all you want to do it via query read on:

Step 1: Execute the code snippet

–Step 1: Execute the code snippet

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

Step 2: Create the excel file and then add the headings in the .xls file.

– Create one Excel File in c:\Test.xls and rename the Sheet1 to Emp. The Sheet should contain 2 columns EmployeeID,Title

Step 3: Run the following query to export data from sql server to excel

USE [AdventureWorks]

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0′, 'Excel 8.0;Database=c:\Test.xls;','Select * from [Emp$]')

SELECT EmployeeID, Title FROM HumanResources.Employee

Points which might interest you:

1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\Test.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).

2. Instead of "Emp" replace it with your excel worksheet name.