I was recently talking with a friend of mine about how best to edit some values in a Microsoft SQL Server table. There were several hundred rows in the table and outside of attaching to it via Access there was talk of writing a “quick” program to do it, etc. This quickly turned to talk of using MS SQL Server Management Studio to directly edit the table which of course you can do [if you have SQL SMS] just by right-clicking on the table and selecting “Edit Top 200 Rows”. But what if you have more than 200 rows?! Well, that’s why I’m writing this post – a quick tip for getting around that 200 row limit (warning, this can be a bit of a forehead smacker).
So the first thing you’ll need to know is how to get into this “edit mode” using MS SQL Server Management Studio. We’ll assume you know the basics of this so just open it up and expand the database tree until you can see your database and the tables under it. Then right-click on the table you want to edit and you should see something like this:
Click on the “Edit Top 200 Rows” option and this will open the table up in a grid that is completely editable. You can edit existing values, delete rows, and add new ones. The trouble of course is that if you have more than 200 rows you won’t see them. Here’s how to get around that problem.
With the table now open for editing you’ll see a few new options lit up in the toolbar above the table. You’ll want to click on the button that says “SQL”:
This will open up the T-SQL editor that controls the view of the data you’re editing in the table. You can see in the query that there is a “Top (200)” clause; that’s what’s limiting you to just the editing the top 200 rows in the table. Guess what happens if you delete that clause? Yup, no more limits:
Just be sure to click the Execute button (or type “CTRL+R”) in order to refresh the table and then you’ll see (and be able to edit) the entire contents, not just the first 200 rows!
Now, you may be wondering if that’s really all there is to it or if there’s maybe a little bit…more. Well, you’ve seen how easy it is to remove the # of rows limit but did you know you could made other edits to that query? You could, for example, add a “where” clause to limit the number of rows based on a field value so if you only wanted to edit the customers in Georgia you could add..
<em>Where Customers.State = 'GA'</em>
and then hit that Execute button and the rows would be filtered to just show the Georgia customers! The possibilities, as they say, are endless!