Exporting data from SQL Server Management Studio
Part two 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 (this blog)
  3. Using the Export Wizard in Management Studio

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.

Saving or Copying the Results of a Query

Perhaps the most flexible way to export the results of a query is to execute it and then either save or copy the results.

Copying Query Results

Once you've executed a query you can use the Results panel at the bottom of the screen to choose what to do next.  The first step is to make sure that you've selected all of the results!

Selecting all results

Click the top left corner of the grid to select all of the results.

 

Once you've done this you can right-click in the same place and choose what to do next.

Copying results

You can choose Copy to get just the records or Copy with Headers to include the column headings.

 

All that remains is to paste the copied results into another application.  In the example below we're pasting the results into an Excel workbook:

Pasting into Excel

There are many ways to paste in Excel - here we've right-clicked on a cell but you could also choose Home > Paste from the ribbon, or just press CTRL + V on the keyboard.

 

Now you can format the results to your heart's content.

Saving the Results of a Query

Rather than copying and pasting the results of a query, you could just choose to save them into another file.

Saving query results

Right-click the top left corner of the grid and choose the option shown here.

 

You can choose between two different file types when saving the results of a query, as shown in the diagram below:

Choosing file type

The default choice is CSV (Comma delimited). Here we're choosing Text (Tab delimited) as some of our film names contain commas.

Opening a Text File in Excel

Once you've saved the file you can open it in another application.  In Excel you can press CTRL + O on the keyboard to choose to open a file.  The diagram below shows what to do next:

Opening file in Excel

The numbered steps are described below.

  1. Choose the type of file you want to open.
  2. Browse to the location in which you saved the file.
  3. Select the file.
  4. Choose to open it.

In Excel, performing the above steps will launch a wizard which leads you through the process of opening a text file.  In the first step you should say that your file is delimited rather than fixed width, as shown below:

Choosing delimited

Choose the option shown here then click Next.

In the next step you can choose which character delimits the columns in the output of the query.

Choosing delimiter

In Management Studio you can create comma-separated value files, or tab-delimited files. Tick the box which matches the type of file you created.

 

In the final step of the wizard you can set basic formatting options for the columns you have imported.

Formatting columns

Click in a column at the bottom of the screen and choose the formatting option from the top. Here we're formatting the film release dates as dates.

Once your file is open you can format it in any way you like.

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