• SQL Prompt
• Exploring and Refactoring Databases

Reusing Query Results in SSMS

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 a 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.

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

Exporting Query Results to 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 query results to a table create/insert script

Often, I’ll need to save the results of executing a query, function or procedure in a temporary table, and then 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 compare key values between the same table on two servers. I’ll 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.

Original publication: June 12, 2018

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more