27 September 2016
27 September 2016

Quick SQL Prompt tip – using the ii snippet

SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the “ii” snippet that helps with inserting data into a table.

If I type “ii” and hit tab, I quickly get the INSERT INTO code added to my query batch. You can see the snippet after typing “ii” below.

SQL-Prompt-ii-snippet-1

Typically, I don’t see this because I can type “ii” and hit TAB very quickly. While this doesn’t take much more time than typing “ins” and then TAB, the simple “ii” just feels like I’m coding more efficiently. Once I do this, it results in a template of the code and a list of tables:

SQL-Prompt-ii-snippet-2

To select a table, I can click one or scroll down with the mouse, but I tend to use the keyboard. If I start typing the first letter of a table, for example, “a”, I get the list quickly limited to just those tables:

SQL-Prompt-ii-snippet-3

Now if I type TAB to select the “Address” table, I will get the full insert statement, with my cursor in the first column value. In the image below, my blinking cursor is actually between the quotes for the AddressLine1 column:

SQL-Prompt-ii-snippet-4

Now I can enter the data values I need, and easily insert those into a table. If I need multiple rows, I’ll copy and paste the section after VALUES and I separate each set of parentheses with a comma.

There are a number of built-in snippets for SQL Prompt, and many of them are very simple, but they can greatly speed up your T-SQL coding if you learn what they are. I’d suggest you download our SQL Prompt Quick Reference Guide and practice using a few of these shortcuts in your code.

I also cover a few other snippets including CDB (CREATE DATABASE) and SSF (SELECT * FROM) in the new series of #SuperSQLTips videos. Take a look and you’ll find handy tricks from other Data Platform MVPs and SQL Server experts too:

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 @way0utwest’s blog on using the ii snippet with #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2dcy0WP center>

Tools in this post

SQL Prompt

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

Find out more

Share this post.

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

You may also like

  • Article

    The SQL Prompt Functionality Finder

    You’ve got SQL Prompt, but are you aware of all the things it can do and how to get Prompt to do it? In this article, Phil Factor provides a handy table to make it obvious. As soon as you open SQL Prompt and start typing, its IntelliSense suggestion window will pop up, offering context-specific

  • Article

    Finding code smells using SQL Prompt: TOP without ORDER BY in a SELECT statement (BP006)

    Using TOP in a SELECT statement without a subsequent ORDER BY clause is legal in SQL Server, but meaningless because asking for the TOP 10 rows implies that the data is guaranteed to be in a certain order, and tables have no implicit logical order. You must specify the order. In a SELECT statement, you

  • 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

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

    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

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly