WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 520 reviews for our classroom and online training
Exporting data from SQL Server Management Studio
Part three of a three-part series of blogs

While SQL Server Management Studio is a powerful tool for writing queries, it has absolutely no concessions towards making the results of those queries remotely presentable! This blog describes several techniques for getting the results of a query into another application so that you can format them to your heart’s content.

  1. Exporting Query Results in SQL Server
  2. Saving or Copying the Results of a Query
  3. Using the Export Wizard in Management Studio (this blog)

This blog is part of our online tutorial in SQL.  Wise Owl also run classroom-based training courses in SQL.

Posted by Andrew Gould on 04 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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.

Saved Excel file

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:

Starting export wizard

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.

Suppressing message

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:

Choosing data source

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.

Choosing destination

The options on this page will be different depending on the type of destination you select. The numbered steps are described below.

  1. Choose the type of destination.  This could be another SQL Server database, an Access database, a text file, or an Excel workbook.
  2. Click here to locate and select the file your data will be exported to.
  3. For Excel you can choose which version you are using.  Excel 2010 users should select the option shown here.
  4. 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.

Choosing which data

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.

Choosing tables

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.

Typing query

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.

Editing mappings

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.

Choosing when to run

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:

Finished export

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.

Exported data

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.  This is especially true if you want to set up a scheduled import/export of data in a SQL Server database.

  1. Exporting Query Results in SQL Server
  2. Saving or Copying the Results of a Query
  3. Using the Export Wizard in Management Studio (this blog)
This blog has 0 threads Add post