134

Aside from context menu -> "Edit Top 200 Rows" from Object Explorer, is there a quick way to open a table in Edit mode where I can just quickly modify the value of a cell?

I need to be able to page past the first 200 rows. And I dont want to write "insert" script for every minor tweak I need to do... I don't understand why SMS doesn't offer quick access to a paged table editor (like Navicat).

Improve this question

4 Answers 4

Reset to default
161

In Mgmt Studio, when you are editing the top 200, you can view the SQL pane - either by right clicking in the grid and choosing Pane->SQL or by the button in the upper left. This will allow you to write a custom query to drill down to the row(s) you want to edit.

But ultimately mgmt studio isn't a data entry/update tool which is why this is a little cumbersome.

Improve this answer
5
  • 12
    If you allow your user to do something (like edit the tables), it should be easy to do so. But that's just my opinion.
    – data
    Commented Nov 8, 2012 at 14:33
  • 7
    This only works if you are using a single table, i.e. no joins. Once you start joining 2 or 3 tables, you can no longer do this. It would be interesting if there were any other solutions.
    – Francis Rodgers
    Commented Feb 28, 2013 at 16:02
  • Yeah, if you join in another table it changes the cells to Read Only :/
    – MetaGuru
    Commented Aug 12, 2013 at 20:21
  • 7
    And when you want to execute the SQL its NOT the usual "execute" button (F5) - its right click on the pane > "Run query" (Ctrl-R)
    – BobbyTables
    Commented Jun 2, 2017 at 4:19
  • If "mgmt studio isn't a data entry/update tool" - then which is? Genuine interest, no hidden sarcasm. I would love a tool like that.
    – Alex from Jitbit
    Commented Jan 19, 2023 at 17:25
60

Go to Tools > Options. In the tree on the left, select SQL Server Object Explorer. Set the option "Value for Edit Top Rows command" to 0. It'll now allow you to view and edit the entire table from the context menu.

Improve this answer
4
  • 22
    ...which is not necessarily a very good idea, if your table has millions of rows.....
    – marc_s
    Commented Oct 8, 2009 at 5:09
  • why don't just enter the desired value for edit Top (n) Rows ie I entered 1000 rows from "Server Object Explorer" option as described by Adam.. giving +1
    – panky sharma
    Commented Jun 26, 2012 at 8:03
  • 2
    @marc_s - yes, but is there a way to edit the sql which gets the top N rows for editing ? Got it - Just choose "Edit Top xyz rows", press Ctrl + 3 in the edit grid region (or click "Show SQL Pane") and edit the query... But please note that this will work only for the query that doesn't contain "join"
    – stack1
    Commented Sep 22, 2015 at 23:20
  • I don't think this allows you to edit the table from the context menu. If I right click a row returned from a select query, there's no option to edit the value (at least in SSMS 18).
    – eyelesscactus54
    Commented Aug 30, 2021 at 19:01
3

Brendan is correct. You can edit the Select command to edit a filtered list of records. For instance "WHERE dept_no = 200".

Improve this answer
2
  • This answer could be improved if it said what to click on to edit the query. I am doing edit top 200 and then I want to edit the query. I don't know how to do that.
    – Be Kind To New Users
    Commented Oct 7, 2020 at 21:51
  • 1
    To bring up the edit-grid's sql editor, just press Ctrl-3 while focus is on the grid.
    – matty
    Commented Jan 27, 2021 at 14:20
1

I know this is a old post but I figure to simplify the answer which I saw Paul and matty answered.

So yes you can change "Value for Edit Top Rows command" to 0 but that will list out everything you don't need. I personally like using "WHERE" so I can target specifically what I need.. so this is what I have done with SMSS v19.3.

  1. I do the "Edit Top 200 rows" by right clicking the table I want to modify.
  2. I press Ctrl+3 (This assumes that table that popped up is selected). This control will open the query box which you can now edit.
  3. Add the "WHERE" targetting the field you want to filter for. You can also remove the "TOP (200)" if you wish but since I'm target for a specific value, I normally don't have 200 of those records.
  4. Refresh the table by pressing Ctrl+R or right click on the table and click "Execute SQL"
  5. Now you can double click the value you want to edit.
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.