This is a nifty little shortcut to help quickly edit multiple lines of code in a TSQL query window. I’ve personally found this TSQL shortcut extremely useful, and I hope you will too.
For this tip, I created a small scenario that includes a list of account numbers we might receive from the finance team to investigate further by running some queries.
I’m going to copy that list of account codes from the spreadsheet finance provided. With my simple select statement, I just want to create “In.” I will open parentheses at the end of the statement, add my single quote, and then paste those values.
And here is the tip that saves some time for finishing this list. GP always likes to give you these lists with many, many spaces in them, so the first thing I’m going to do is replace those empty spaces with a single quote. Go to the end of the list, including all of those spaces. Now hold the shift key, and select all those empty spaces from that bottom line, the last account number in the list.
Now that I have that highlighted, here’s the tip.
Push and hold the shift key. While doing that, push and hold the alt key, so you have the shift and alt held down right now. Then push the up arrow to move up in the list. So I will leave the top one out of it for now. If I don’t, it will highlight the last number and replace it, which we don’t want. Try it for yourself so you can see. When I let go of my shift and alt, I now have all of those lines highlighted in the same location.
Now hit the single quote key, which will replace all of those empty spaces with a single quote.
Now, manually add a single quote to the end of the top one that we ignored earlier.
We are not quite done yet. We need to also add a comma to separate the list, and another single quote to identify the value. So again, put the cursor where you want to start. Press and hold the shift and alt keys, and then hit the down arrow. Now we can add a comma and a single quote, and finally close our parentheses here.
Thus, we have a very simple way of editing multiple lines in TSQL.
A Second TSQL Shortcut Example
Here’s a different example of how you can use the same TSQL shortcut.
Let’s assume the question was to look at some sales order processing lines for these particular accounts. Sales order processing doesn’t have the account number, but it does have the account index. I can get the account index based upon the list that finance gave me and the sales order table.
With these indices, I can create an “In” clause.
Since I’ve already copied the Account Index column from the bottom, I can now paste it up here in the “In” clause.
Close the parentheses at the end of the last number, and then move the cursor to the beginning of the line for the last index in the list. We need to put in a separator, so we will once again hold the shift and the alt keys and go all the way up to the second number from the top, highlighting those rows, and add a comma, to all rows at once.
Now I can quickly select everything from that table where that index is (as shown above).
This particular example is for demonstration purposes only. We could have certainly joined SOP Lines to the GL table, but I wanted a simple example to show this tip. We’re using this to show the shift-alt method of editing multiple lines. Of course, this trick can be used in multiple places, not just when it comes to editing multiple lines of TSQL code. It may work in Word, Notepad, Outlook, etc… It may also be version specific so if it does not work for you it may be an older version of SQL.
Have any questions about this tip or do you have your own tricks for editing TSQL? Please contact us and let us know!
