Product articles
SQL Prompt
SQL Refactoring
SQL Prompt Hidden Gems: The SSMS…

12 June 2018


Guest post

This is a guest post from Louis Davidson.

SQL Prompt Hidden Gems: The SSMS Results Pane

Every time you need to reuse the query results from SSMS, for example to populate another table, or to search for matching rows in another table, it will inevitably mean a lot of manual tweaking to the get the results into the right format. Louis Davidson uncovers three hidden Prompt gems (Open in Excel, Script as INSERT, Copy as IN clause) that can remove all this pain.

Guest post

This is a guest post from Louis Davidson.

Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use them elsewhere, like in document or as input to other queries. I’ve always found this an awkward and time-consuming process.

For example, if I want to include the query results in a document or email, it will inevitably involve a lot of manual tweaking to get the results into a readable format. If I need to use the query results to populate another table, or to search from matching rows in another table, it always entails 10-15 minutes messing around with formatting the output, especially commas and quotation marks, to get a working query.

Fortunately, hidden within SQL Prompt’s right-click context menu, when you’re working in the SSMS grid format results pane, are some features that can help. Two of them, Copy as IN clause and Script as INSERT, will ‘refactor’ query results into code that you can use in another query, and the third, Open in Excel will export the results directly to Excel, for reporting.

As I have written this series of articles about SQL Prompt, discovering new features, I generally tell my coworkers about them. No other features have extracted quite the same squeak of excitement from them as these.

The Query

To demonstrate how I use each of these features, typically, I will use the following simple query:

It returns the following rows:

RowId GuidValue StringValue
1 00f8c491-7811-49c7-8993-a74f2b505122 First
2 6bf22b63-5be4-475a-8bc9-51f1d72aaad7 Second

Opening the Results in Excel

Several times a week, I need to include query results in emails or report documents. If I just select the required results in the grid, using the built-in Copy with Headers… and then paste it directly into a document, I no longer have a nicely-formatted tabular output. Instead, it’s tab-separated text, and the longer and more complex the output, the more trouble this will be to format nicely:

RowId GuidValue StringValue

1 55989EB5-601C-4DCA-871F-7CE3B61BFD34 First

2 EB553945-2E91-4C35-AE68-5D6840FA6A6F Second

Copying the results from the text output mode in SSMS, gives a more readable result, but then it is space-aligned, requiring you to use a fixed-width font. This is OK when communicating with other members of a technical team, but not the wider business. The way around this is to copy the grid results into Excel, format them nicely, and then either send the Excel document, or copy and paste from there into the mail, or any other document.

Much easier though to let SQL Prompt open the results directly into Excel. Simply highlight the rows, right click, and choose Open in Excel as seen in Figure 1.

Figure 1

This will an Excel spreadsheet containing the data, as shown in Figure 2.

Figure 2

Now simply format the text as desired and paste into your document, letting Excel handle word wrap, gridlines, and so on.

RowId GuidValue StringValue
1 968d13ed-09f7-46cf-8455-6744bc8d8cdb First
2 c79eb2d6-18f2-44a5-8ed2-6df846f9438b Second

You don’t need to export all the results; you can work with partial sets of the data in the grid, such as only two of the three columns, if the selected columns are adjacent.

Scripting the results to a table create/insert script

Often, I’ll need to store the results of executing a query, function or procedure in a temporary table, and manipulate that data in some way, such as aggregating values from a procedure. At other times, I’ll need to script the results of a query into a multi-row VALUES clause, so that I can create a quick copy of the table, for example to move data from one server to another.

There are a few ways to get the results formatted for reuse, including writing T-SQL that uses messy CONCAT expressions to output. For example, for a query using a CTE, I could produce code that will replicate the output of the rows in the VALUES clause of the sample query (to get the complete INSERT statement is straightforward enough):

Also, if the output is from a stored procedure, I can direct the first result set to a temporary table, with some effort. However, instead of jumping through such hoops, SQL Prompt will simply generate the output with a right click, saving you a ton of time whenever you need to perform these tasks.

In Figure 3, I’ve selected just the last two columns of data, then right-clicked and chosen Script as INSERT.

Figure 3

This will open a new query window in SSMS, containing the following script:

I can use this script, as is, to create a temporary table of data that I can manipulate. I can also see the data types of the output columns, which is useful. You can select columns from multiple tables; SQL Prompt will script whatever data is in the grid, using the datatype of the columns in the result.

Oftentimes, I don’t so much need the #temptable, rather I just need to grab the VALUES clause, and use it to load a copy of the table from which I selected the data. For example, if I want to duplicate the data from one table, in a table on in another server, I just execute SELECT * FROM SourceCopy, then use Script as INSERT feature, and copy the resulting VALUES clause into my own INSERT INTO clause.

Note that if your output is complex, there may be cases, such as duplicate column names in the result, that this simple technique cannot handle. Phil Factor has an article that will help you script out more complex cases: Building reusable table build scripts using SQL Prompt.

Scripting one column to an IN expression

This last feature is one that I use quite regularly, as it directly replaces several annoying, manual tasks with just a few, simple mouse-clicks.

I often need to take a set of key values from a table on one server, and check for their existence in the same table on a different server. For example, say I have an ETL process that duplicates a table from one server to another. On the source server, I select out some data, perhaps the top 100 rows that have been modified today. Now I need to be able to fetch just these same rows on the target server.

To do this, I’ll grab the key column values from those rows, and use them to construct an IN clause that I can use to query the other table, comparing the rows for differences. If I find any, I’ll typically use the previously covered Script as INSERT feature to copy the entire rows from one server to another to compare the data in those rows side by side.

The method I had used many times to get key values was to copy the data from the results, paste it into the query window, and use ALT + Drag to paste in quotes and commas around the data. This can be tedious if you need more than a few rows.

Now, using SQL Prompt’s Copy as IN clause tool, I can easily grab a column of values, and let SQL Prompt take care of all of the formatting. Highlight one column of data, and right click and choose Copy as IN clause, as seen in Figure 4.

Figure 4

Now just paste the results into your query, perhaps using SQL Prompt to reformat it a little, and you are ready to go.

As a more realistic example, consider the following query results from the WideWorldImporters database.

Figure 5

My query returned 73595 rows, and using Copy as IN clause still works seamlessly. I’ve also tried this technique with 100000 uniqueidentifier values and it worked nicely, even if it took about 1 second to paste. When I tried it with over 1 million integers (all with at least 8 digits), it created the script, but it was too large to open in SSMS, giving me the error message you can see in Figure 7, but still allows me to save the file.

Figure 6


As I noted in my previous article on hidden gems in SQL Prompt, not only does SQL Prompt enhance the native IntelliSense features of SSMS, there is a lot of hidden treasure in various parts of the tool. The changes it makes to the behavior of the results pane is another gem of a feature that I use frequently, now I know it exists!

The tools that it adds to the result pane are great time savers, allowing you to format the data in several ways that are invaluable to any SQL programmer. Opening the results in Excel saves a few keystrokes every time, which is nice.

It’s not every day I need to refactor query results for reuse, but each time I do, then using Script as INSERT, or Copy as IN clause, saves me considerable time messing around with formatting the output.

You may also like

  • Community event

    PASSMN (Minnesota SQL Server User Group)

    The Minnesota chapter of the Professional Association for SQL Server (PASS). PASSMN is a forum to exchange knowledge and ideas to better understand, develop, implement and support existing and future SQL Server technologies. Our technical sessions provide a professional, fun and educational environment for all levels of expertise. Redgate are sponsors of their February meeting

  • Article

    Removing the Square Bracket Decorations with SQL Prompt

    If you avoid illegal characters and reserved words in your identifiers, you'll rarely need delimiters. Sadly, SSMS applies square bracket delimiters indiscriminately, as a precaution, when generating build scripts. Phil Factor provides a handy function that adds quoted delimiters only where they are really needed and then sits back and lets SQL Prompt strip out any extraneous square brackets, in a flash.

  • Article

    Beyond Formatting: Improving SQL Code using SQL Prompt Actions

    Louis Davidson explains how he uses the SQL Prompt refactoring 'Actions' to qualify object names, standardize the use of aliases, and make other instant changes that improve the overall quality of his SQL code.

  • Community event

    PASS Deutschland eV - Berlin chapter

    Redgate will be remotely sponsoring the October meeting of the PASS Deutschland – Berlin chapter. PASS Deutschland eV is the German Microsoft Data Platform Community, ie topics from this environment are treated within the framework of the association. Not only do we rely on the SQL Server and its many services, tools and components, we also deal with

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly