16 March 2017
16 March 2017

3 Results Grid features SQL Prompt brings to SSMS

Guest post

This is a guest post from Cathrine Wilhelmsen. Cathrine is a Friend of Redgate and Microsoft Data Platform MVP, working as a Data Warehouse and Business Intelligence consultant.

She loves teaching and sharing knowledge, and is active in the SQL Server and PASS communities as an author, speaker, blogger, organizer and chronic volunteer.

She can be found on Twitter and she regularly blogs about technology issues.

You may not have heard about it yet, but three helpful features were recently added to SQL Prompt that are activated when you right-click on the Results Grid in SQL Server Management Studio (SSMS).

1. Copy as IN clause

This feature is available when you have values from a single column selected. You can select the entire column, parts of the column, or even single values in the column. Right-click and select Copy as IN clause.

The values you selected will be formatted as a list that you can paste into a query:

SSMS Results Grid 1

2. Script as INSERT

When you select the entire grid, adjacent rows, adjacent columns, a single cell, or a block of cells, this second feature is now available. Simply right-click and select Script as INSERT.

A new tab is automatically created with a script containing the values you selected. This script creates a temp table with the column names and data types from the original query. It inserts the selected values and drops the temp table. This is such a time-saver for me, even if I only need the INSERT part of the script.

SSMS Results Grid 2

3. Open in Excel

When you select the entire grid, adjacent rows, adjacent columns, a single cell, or a block of cells you can also right-click and select Open in Excel.

This opens a new Excel file with the selected values and column names in the first row. This is perfect for when I quickly need to send some data to our business users:

SSMS Results Grid 3

Now watch the video

If you’d like to see a demo of the new features in action, watch this short video:

Bonus tip

If you don’t like the way the auto-generated code looks, by the way, you can also use the new SQL Prompt formatting options to make it more attractive or better fit the style your team uses.

If you’re not a SQL Prompt user, download a free trial and see how much more productive you can be writing code every day.

Like this? Share it with other SQL professionals using the social sharing buttons below. Simply tweet: I’ve just read @cathrinew’s blog on 3 useful Results Grid features in #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2mBQ1lr

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Guest post

This is a guest post from Cathrine Wilhelmsen. Cathrine is a Friend of Redgate and Microsoft Data Platform MVP, working as a Data Warehouse and Business Intelligence consultant.

She loves teaching and sharing knowledge, and is active in the SQL Server and PASS communities as an author, speaker, blogger, organizer and chronic volunteer.

She can be found on Twitter and she regularly blogs about technology issues.

Share this post.

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

You may also like

  • Article

    SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

    Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. SQL Prompt Code Analysis rules include an Execution rule, E1029, which will alert you to use of this function, in your T-SQL. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or

  • Article

    Using multiple custom SQL formatting styles in SQL Prompt

    Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. In my previous articles on SQL Prompt, I explained why a team of programmers might want to adopt a T-SQL coding standard, how SQL Prompt could make this easier, and then how to use

  • Article

    Finding code smells using SQL Prompt: procedures that lack schema-qualification

    SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified. PE001 – Schema name for procedure is not specified Even where you don’t have to qualify the name of

  • 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

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly