BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Exporting Query Results in SQL Server
- Saving or Copying the Results of a Query (this blog)
- Using the Export Wizard in Management Studio
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!
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.
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:
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.
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:
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:
The numbered steps are described below.
- Choose the type of file you want to open.
- Browse to the location in which you saved the file.
- Select the file.
- 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:
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.
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.
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.