This is the first post in what I'm calling Quick Tips. The intention for small tips and tricks that don't require a long explanation or setup. Today's tip is regarding editing rows in SQL Server Management Studio. For this tip, I'll be using SSMS 2014 version 12.0.5203.0. I also downloaded the backup for AdventureWorks from here. To edit some data you have a few options. One of which is simply to write out update statements and execute them against you database. That approach works fine you only have 1 or 2 rows to update or all the rows need to have the same columns updated to the same value and can be covered by the same where clause. If you more than a couple rows to update and they cannot be covered by the same where clause or need different values in the columns then you need to do something different. One option is to simply right click on the table and edit top 200 rows. What do you do if you have more than 200 rows in your table? You can change the default rows returned (like I did to 300). How to do that is tip 1. Tip 1: How to change the default number of rows for SSMS Edit Top XX Rows To change the default number of rows open the Tools Menu then go to Options. From there go to SQL Server Object Explorer and edit the value for Edit Top <n> Rows Command. You can see from the description underneath that if you enter 0 all rows will be returned. This is a global command and will apply to ever table so I wouldn't recommend changing this to 0. If you do change it to 0 and edit a large table you may notice that SSMS takes awhile to load all the rows and open the query window. So that method works great if the rows you want to edit are in that top XX number of rows. What do if they aren't is today's Tip 2. Tip 2: How to edit multiple rows not in the top XX The other method for updating rows is to use the SQL Plane to edit your query. I normally use this method with the first right click edit workflow. I will right and edit top XX rows on my table. Then I will click the Show SQL Pane button (or ctrl + 3) and edit the query. Clicking that button will open a window similar to this. At the top you can see the select top used to filter the table. It is this statement that you can use to select multiple rows to update. Let's say that I wanted to change the shift ID for all Department IDs of 4. I could just add a simple where clause to the query and click the execute button. Then I can edit each row quickly and give whatever values I needed without writing out a bunch of different update statements. There that's it. We now have 2 new methods to update multiple rows in SSMS. This quick tip was a bit longer than planned, but I hope it helped. As always, happy coding.
Sean Wernimont The Blind Squirrel Copyright 2015-2020
|
AuthorWelcome to The Blind Squirrel (because even a blind squirrel occasionally finds a nut). I'm a full-stack web and mobile developer that writes about tips and tricks that I've learned in Swift, C#, Azure, F# and more. Archives
April 2018
Categories
All
|