Using the Export Wizard in Management Studio
SQL Server Management Studio has a wizard to help you export data from a database, although it's a little long-winded to use compared to the options described in the first two parts of this series.
Preparing to use the Export Wizard
The wizard that you use to export data in SQL Server assumes you have already created a location for the data to be exported. In our example we'll use Excel as the export location, which means that we need to create and save an Excel workbook.
Give your workbook a sensible name and make sure to close it after saving it.
Launching the Export Wizard
You can start the wizard by right-clicking the name of your database in the Object Explorer window and choosing the options shown in the diagram below:
Right-click the database then choose Tasks > Export Data...
By default, the first page you'll see when you launch the wizard explains to you that you've just launched the wizard! You can happily click Next to get to the useful part of the process.
Check this box if you'd like to stop SQL Server informing you when you've started the wizard.
Choosing a Data Source
The first real step of the wizard asks you where your data is going to come from. The options are reasonably self-explanatory, as shown in the diagram below:
The options should be pre-selected to use the database whose name you right-clicked on to launch the wizard.
Click Next to move to the next step.
Choosing a Destination for the Data
The following step asks where you would like the data to go when it is exported.
The options on this page will be different depending on the type of destination you select. The numbered steps are described below.
Choose the type of destination. This could be another SQL Server database, an Access database, a text file, or an Excel workbook.
Click here to locate and select the file your data will be exported to.
For Excel you can choose which version you are using. Excel 2010 users should select the option shown here.
Check this box if you took the time to add column headings to the Excel file you created earlier.
Click Next to move to the next stage.
Choosing the Data to Export
The next step is to choose exactly which data you want to export. You can do this by selecting from the tables and views stored in the database, or by writing a query.
Select one of the options.
If you choose to get your data from tables or views you'll be presented with a set of check boxes for selecting exactly what data you want.
Check the box next to the tables you want. You can click Edit Mappings... to control what gets exported in more detail.
If you chose to write a query to export the data you'll be presented with a basic text box in which you have to write a valid query without the aid of IntelliSense.
You can't even press TAB to indent your code! Clicking Parse will check that your query is valid. You can also click Browse... to open a query that you have previously saved.
Editing Export Mappings
After writing a query and clicking Next you will have the opportunity to click Edit Mappings... to control in more detail what gets exported.
This dialog box allows you to choose the data type of each of the exported columns.
Exporting the Data
The final step of the wizard allows you to choose whether to run the export immediately and/or to save the export steps in an SSIS Package to be run at a later time.
Here we're choosing to run the export immediately without saving an SSIS package.
Saving an SSIS package means that when you want to perform the same export in future you don't have to go through the entire wizard again! You can learn much more about Integration Services on our two-day SSIS course.
Click Finish to perform the export. You should be shown a list of tasks being performed, as shown below:
A successfully completed export.
Checking the Results
All you have to do now is open the destination file to ensure that all of your data is there.
You may find that you have to spend some time tidying up the data that has been exported.
Is the Export Wizard Worth It?
If you're only performing a one-off export the export wizard is not really worth using, especially as it's so much easier to copy and paste query results. If you're going to perform the same export many times however, the ability to save a reusable SSIS package is incredibly useful.
