This is a cool SSMS trick I picked up a while back
Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works.
Pure ALT+SHIFT magic.
Hold down both keys at the same time, and use your up and down arrow keys to navigate vertically. There will be a thin grey line showing you exactly which rows you’ve grabbed. Then Just type normally. I uh, simulated a typing error, to illustrate that you can also delete text doing this. Yeah.
It really makes doing simple multi-line edits a breeze, especially if you don’t feel like setting up Excel formulas to do similar tasks. These are random Massachusetts zip codes, which is why they get a leading zero, and quotes.
Can you feel the efficiency?!
Kendra says: What in the…. holy cow, that actually works!
Brent says: I knew about that trick, but ZOMG PEOPLE THERE IS A PRODUCTIVITY GIF IN OUR BLOG
53 Comments. Leave new
I have been using this lovely little trick for a while. It also works in Visual Studio, for those who use it…
It also works in the PowerShell ISE, if you’re into that sort of thing.
I accidentally used ALT+CTRL instead of ALT+SHIFT and got an unexpected surprise. The entire screen turned upside down!
You’re late to the part :p
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/the-magic-alt-button/
Dammit, that should have been partY 🙂
I would have written it a lot sooner, but I only got hired a month ago 🙁
Let’s just agree it is Brent’s fault 🙂
sweet. and it works in powershell ise too! be gone now oh dreaded carpal tunnel! nice tip new guy.
I was aware of Left Alt with the mouse to highlight and add code, but I am a keyboard kind of guy so that was always a little lacking to me. I had no idea that Alt with Shift made it happen almost exactly the way that I would want (and mad at myself for never trying). You have made my week!
Uh, $%#%$# I just paid $80 for Ultraedit yesterday because of this function essentially. Ok, Ultraedit is still really powerful but it’s column mode I have used many many many times for crafting numerous very similar queries, setting up massive where in lists and so on. Now I can do some of this directly in SSMS. Shhh don’t tell my boss
OK, Not so much. I just tried editing in column mode in SSMS a 74mb log file that my goal is to modify and import into a DB. Tried editing a simple thing at the start of every line and it managed to essentially crash SSMS/Visual studio. So I imagine this works great for small little things, not so much for some of the log files I occasionally have to process and tweak before importing to a DB. Oh, and for some types of log things a product called Log Lizard is pretty cool too.
Again, thank you so much for this awesome trick.
Why. Did. I. Not. Know. About. This. Sooner!?!?!?!? Thank you for sharing this 🙂
Worth mentioning that it works in SSMS 2012+
Unless you’re using SSMS Enterprise Edition 😉
This is very useful, learned it works in Word and other programs too while on interview in 1998. Glad they didn’t mess this shortcut up.
Sweet Merciful Crap!!! The productivity!!!
Awesome! Very useful. Thanks
Aaahh, how many times I cursed while adding those commas one by one! This is freakin’ awesome, thanks for the tip, man!
Wow, socks in deed lost. Great trick. And one I wish I had seen a years ago. Thanks!
Kenneth
When I need to do this sort of thing, I store the values in a PowerShell variable and then use PowerShell commands to wrap parenthesis, add commas, wrap brackets, or whatever is needed. This helps me keep my PowerShell skills sharp.
Where has been this all my life? Thanks for sharing
Awesome trick – I knew about this but had forgotten it.
Wow!
That is interesting, I had no idea that this existed. I do often have the needs to do something like this and I have been using an alternative to accomplish my goal… I see that your method has a shortcoming, and that is that this won’t work if you need to edit the end of the rows for rows which are of varying length (say you wanted to add an apostrophe to the end of each row, where you have a list of all of the 50 states in the USA), this would edit the column position of the row in which you began, and this would not give you the required result.
The method which I use – notepad++. I use its macro functions to do similar stuff, it’s one click for a whole lot of multi-row editing.
Though I do see another application where I can use this trick.. thanks for sharing!
In Notepad++ you can replace (.+) by \(\1\), to get a VALUES list from lines. Handy.
In Notepad++ you can Alt+Select (and Alt+Click) much the same way as on the gif in the post.
What has been done in the gif can be accomplished using regular expressions with SSMS’ Find & Replace (ctrl + H):
Highlight what you want to edit
Find: \n
Replace: ‘\),\n\(‘
Apply to: Selection
Tick: Use regular expressions
Even better just replace ^ with (‘
And $ With ‘)
^ beginning of line
$ end of line
Thanks for pointing regular expression out 🙂
Say wut??? The SSMS trick is cool… Notepad++ I knew about… Ctrl+H is messy.. but the regex for begging and end of line… That just baked my brain. These could have saved me years.. ok days of my life sounding like an ack-ack gun typing in quotes and commas for “in” lists.
I first discovered this ability in MS Word of all places. Back then, VS 1.0 & 6.0 didn’t support it, so I found myself copying code from VS into Word to do the magic and then pasting it back. Ah, the good old days.
This is sweet! I’d always hoped there was a way to do this, but didn’t know it actually existed.
This actually works in many text editors. Additionally you can just use the alt key and then hold the left mouse button as you highlight. This is a little quicker than the arrow keys in some instances. Besides typing what you want repeated, you can also paste a string. Very handy.
And nerds everywhere rejoiced!!!
this is probably as good a time as any to mention the only other ssms trick that has saved me as much time as this one, which is that double clicking on any red error message in the messages tab will take you straight to the offending line in the code…perhaps everyone else in the world already knew about this, but i had no idea until i saw brent do it (thanks brent!)
Great tip!!! although, only for small number of rows (as using arrow key for large number of rows will be time consuming, use Excel in that case).
is the title of this post meant to echo the refrain from the notorious b.i.g. track “party and bullsh**” ? that’s what i heard when i read it, anyway.
Yes. I hope I didn’t ruin it for you. Heh.
quite the contrary! i prefer the Ratatat version, because, how could you not??
Oh my, the amount of time I have wasted hitting the down arrow, comma and then back up to the top to start all over haha. Sent it to all my colleagues!
You can use mouse for this.
Click where the starting point is, then use mouse (roller) or window scrollbar to reach to end, then press ALT+SHIFT and then click at the end point.
This is almost a reason to left-justify commas in SQL.
Almost.
Dam! For some reason it works in my SSMS 2012 (11.0.5058.0) but not SSMS 2008 R2 (10.50.4000.0). I keep SSMS 2008 R2 around b/c you can’t edit management plans that are created in SSMS 2008 R2 with SSMS 2012 and a few others…BTW all my dbs are 2008 R2.
Its very nearly brilliant, how about appending to every line when they are different lengths
Brilliant!
Perfectly handy… just read this a day before i needed it to catch up on a way-behind log shipping scenario.
Life-improvingly good.
Does not work in any my SQL 2008 R2 environments. Guess I will have to continue to do REPLACE ALL in Word. I usually copy results or text from SSMS to Word (sometimes Notepad as an intermediary if there are a bunch of internal tabs) and then do REPLACE ALL ^p (paragraph marks) or ^t (tabs) with my text.
Other cool trick that works in SSMS and VS is to hold down the ALT key while selecting a block of text from several lines.It selects from the point of selection, not from the start of the line and the entire line. Good for copying text that is tabbed and you do not want all the leading white space.
While this trick can save you a ton of time and effort, I think we can all agree that its best use is to make your coworkers stare at you like you’re some kind of wizard.
John – HAHAHA, agreed. And blog readers.
Yeah. Any trick that makes it easier to hardcode huge lists of literals into the SQL has got to be good… right? Some #!@king dufus at my work (don’t know who yet) created a list like this, only it contained 22,000 elements. I’m shuddering at the thought that they’ll discover this.
[…] Vertical block editing: Shift + Alt + Up/Down Arrow Key or Alt + Up/Down Mouse Drag (occasionally very useful, see it to believe it) […]
Did you know there’s also a way to edit the end of multiple lines of varying length? For instance, if you wanted to add a comma at the end of a list of tables without extra white space after the shorter table names. I stumbled on a way!
Paste the list into a fresh SSMS query window – without any tabs or spaces in front of any values. Use the Alt and drag the cursor down as if you were just going to add to the front of each all values in the list. Now, press Enter, then press Backspace, then press End. Your cursor should now be at the position of each value, and you can add what you need to. I use a combination of both tricks to do things like grant permissions on a list of tables, add multiple users to a database role on the fly, etc.
It can behave a little strange if you do have spaces or tabs in front, as you’ll need to hit Backspace multiple times before hitting End, so it’s easier to just try and do it in a fresh query window then copy into wherever you need it if you need indentations for formatting.
Hey Brent, I’m not saying my video about this is cooler, but…
https://www.youtube.com/watch?v=FWIGmiirL_8
Good, because I’d have to disagree with you if you said it. That trick doesn’t need a >60 second video. 😉