SQL Prompt Hidden Gems: The SSMS Results Pane

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

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

Summary

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.

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Prompt as a Layout Tool: A Survival Guide

    Here is some SQL code to reveal the highest-performing salespeople at AdventureWorks and listing their managers. SELECT SalesValue, COALESCE(SalesPerson.Title+' ', '')+SalesPerson.FirstName +COALESCE(' '+SalesPerson.MiddleName, '')+' '+SalesPerson.LastName +COALESCE(' '+SalesPerson.Suffix, '') AS SalesPerson, COALESCE(BossPerson.Title+' ', '')+BossPerson.FirstName +COALESCE(' '+BossPerson.MiddleName, '')+' '+BossPerson.LastName +COALESCE(' '+BossPerson.Suffix, '')+ ' ('+boss.jobtitle+')' AS manager FROM(SELECT TOP 10 SalesPerson.BusinessEntityID AS salesPerson_ID, SUM(SalesOrderHeader.TotalDue) AS SalesValue FROM Sales.SalesPerson

  • Article

    Consider using [NOT] EXISTS instead of [NOT] IN (subquery)

    It used to be that the EXISTS logical operator was faster than IN, when comparing data sets using a subquery. For example, in cases where the query had to perform a certain task, but only if the subquery returned any rows, then when evaluating WHERE EXISTS (subquery), the database engine could quit searching as

  • Article

    How to record T-SQL execution times using a SQL Prompt snippet

    A SQL Prompt snippet is a pre-defined block of code, which we can insert at the current cursor position, or around currently-selected SQL text, in an SSMS query pane. DBAs use them to store all their most commonly-used checks and routines. It’s often useful to have your favorite troubleshooting scripts available to you within a

  • Article

    SQL Code Analysis from a PowerShell Deployment Script

    Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in ‘temporary’ development code, by mistake, so it is always good to have a way of flagging up SQL Code issues and ‘smells’ that are agreed to be incompatible with ‘production’

  • Article

    Building reusable table build scripts using SQL Prompt

    You’ve been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, and such things can be tiresome to do. Consider the following AdventureWorks query. SELECT * FROM Sales.Customer INNER JOIN Person.Person

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly

  • Matt Caldwell

    What version of SSMS is this? 2014 doesn’t appear to have these features…

    • Stephen Scott

      I, too, use SSMS 2014. These particular features are a product of the SQL Prompt add-on, for SSMS.

  • Debbie Jacob

    Thank you for sharing! Those will be life changing!

  • Stephen Scott

    I have used both features, since they were released, and absolutely love them.