3 Results Grid features SQL Prompt brings to SSMS
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:
This is useful for tasks such as checking the existence of certain rows, from their key values, in another copy of the same table (e.g. the same table on two different servers). For more details, see:
- Copy as IN clause documentation
- Product article: Scripting one column to an IN expression
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.
For further details, see:
- Script as Insert documentation
- Product article: Scripting query results to a table create/insert script
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:
For further details:
- Copy in Excel docs
- Product article: Exporting Query Results to Excel
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
You may also like
-
Article
Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)
If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the
(MAX)specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and simply don't know the correct length of each string. -
Article
SQL Prompt Code Analysis: Table does not have clustered index (BP021)
If SQL Prompt alerts you to a table without a clustered index, investigate the reason for its absence carefully. It is rare indeed to find a table where data retrieval is faster without one.
-
Article
Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)
Phil Factor explains why you should prefer use of EXISTS over IN, when comparing data sets using a subquery. While there is no longer any significant performance advantage, using NOT EXISTS will avoid unexpected results when the subquery’s source data contains NULL values.
-
Live training session
Level up with SQL Toolbelt Essentials: Standardizing development with SQL Prompt and SQL Toolbelt Essentials
In this session, get an overview and an introduction into each of the 10 tools included in SQL Toolbelt Essentials as well as discovering the enhanced SQL Prompt features specifically for SQL Toolbelt Essentials, which include The Platform and Bulk Actions. You'll also learn how you can standardize and scale development practices across teams and pave the way for automation with version control.
-
