SQL Server Export to Excel using SQL Server Import and Export Wizard

Data export to Excel from SQL Server has always been a problem for T-SQL developers and SQL Server administrators. Microsoft SQL Server professionals have now advanced sql tools for solution to SQL Server export to Excel within SQL Server Management Studio. The latest SQL tool to export data to Excel from SQL Server is SQL Server Import and Export Wizard.

Since I've read many forum posts asking how to export to Excel from SQL Server, I want to show step by step how sql developers can use SQL Server Import and Export Wizard in order to export data from SQL Server to Excel file.

The sample case in this SQL tutorial is created using Microsoft SQL Server 2008 R2 version with AdventureWorks SQL Server sample database.

SQL Server Import and Export Wizard

SQL professionals can open SQL Server Import and Export Wizard within SQL Server Management Studio (SSMS).
Open SQL Server 2008 R2 SSMS, connect to the SQL Server instance you want to export data from
Drill down to the SQL Server database you want to export data to Excel file.
As seen in the below screenshot, right click on the sql database name and select Tasks > Export Data option.

export-data-from-sql-server-to-excel

When sql developers select Export Data option from Tasks menu, the SQL Server Import and Export Wizard will be started.
Here is the sql tool startup screen.
We will use the export function of the SQL Server Import Export wizard to copy data from database table to Excel spreadsheet.

sql-server-import-and-export-wizard

As Datasource, SQL administrators will choose SQL Server instance name to export data from.
Choose the sql database name after the SQL Server instance is selected and database names are populated into the dropdownlist.

sql-server-export-to-excel

As target destination Excel file will be selected. Choose Microsoft Excel in the Destination input area.
Then point to the Windows folder and Excel file for the export to Excel task.
You can either choose an existing Excel file or a new file which has not already been created can be set as destination Excel file.

destination-excel-file-for-data-export-from-sql-server-database-table

SQL Server Import and Export Wizard grands two options for sql professionals in this step of the export task:
Copy data from one or more tables or views, or
Write a query to specify the data to transfer.

If you want to transfer data at that moment from SQL Server to Excel file, select the first option as I'll do too in this sql tutorial.
Copy data from one or more tables or views option will export to Excel file from data source SQL Server database tables and views.

copy-data-from-sql-server-table-to-excel-file

The next screen in SQL Server Import and Export Wizard enables sql developers to select datasource tables or views for export to Excel task.
SQL administrators can select the required sql database tables and views here by marking the checkbox beside the table and view names.

For this sql tutorial, I choosed to use HumanResources.Department table since it does not too many rows. But you can select the Person.Person table from SQL Server sample database AdventureWorks, it has much more data rows and works successfully with data export task using SQL Import and Export wizard.

source-table-for-export-to-excel

The Review Data Type Mapping screen can be used to convert sql data columns into types that are compatible with Microsoft Excel. SQL Server 2008 R2 is capable to export to Excel in many data types.

sql-export-to-excel-data-type-mapping

Save and Run Package screen enables sql developers to save the data export task as SSIS package to use later.
For this SQL tutorial, I want to run the sql export to Excel task immediately. So I'll choose "Run immediately" option. Later, as a second sql tutorial on data export, I'll choose "Save SSIS Package" and save the SSIS package on the local SQL Server Integration Services.
Note : Please refer to Export Data to Excel using SQL Server Integration Services SSIS Package sql tutorial for SSIS sample.

run-immediately-sql-export-to-excel-task

This screen enables a last check and verify task option for SQL Server professionals.
You can review the data source SQL Server instance and sql table, destination Excel file here before proceeding with the task execution step.

verify-data-export-task-from-sql-server-to-excel

This is the last screen in the SQL Server Import and Export Wizard.
This screen execute SSIS package created to export data from SQL Server to Excel file. With each step processed, sql developers and administrators can track the export execution, view messages displayed by sql engine.

execute-data-export-to-excel-from-sql-server-task