Editing Data Directly in SSMS

Query results in SQL Server Management Studio are read-only by default. Follow these steps to edit SQL Server table data directly inside SSMS.

Editing Data Directly in SSMS

In Microsoft Access, you can edit table data by double-clicking the table name.

To get this same feature in SQL Server Management Studio (SSMS), you can right-click the table name and choose "Edit Top 200 Rows."  But what if you want to edit a different set of rows?  

Follow the steps below to choose the rows you want to edit in SSMS.

Edit Top 200 Rows

We'll start by using the built-in "Edit Top 200 Rows" feature.

Right-click the table name and choose "Edit Top 200 Rows":

This will show an editable datasheet view of the top 200 rows of the table:

Query Designer Mode

Tabs opened via the "Edit Top 200 Rows" feature open in "Query Designer" mode.  This mode adds a "Query Designer" menu to the menu bar:

There are four different "panes" that you can show or hide in "Query Designer" mode:

  • Diagram (Ctrl + 1)
  • Criteria (Ctrl + 2)
  • SQL (Ctrl + 3)
  • Results (Ctrl + 4)

Choosing Which Rows to Edit

The simplest way to change which rows are shown in the datasheet view is to update the SQL directly.  To do this, press:

  • [Ctrl] + [3] to display the SQL Pane

This is analogous to the "SQL" view when editing queries in Access.  Here's what my tab from above looks like after displaying the SQL Pane:

Execute SQL - [Ctrl] + [R]

One important thing to note is that there is a different shortcut key ([Ctrl] + [R]) when executing SQL in Query Designer mode versus a traditional query tab (which uses [F5]).

Changing the SQL and pressing [Ctrl] + [R] will refresh the data shown in the Results pane.

Query Design View

I prefer to edit the SQL directly.  If you're not comfortable doing that, though, you can press:

  • [Ctrl] + [1] to open the Diagram Pane, and
  • [Ctrl] + [2] to open the Criteria Pane

These two panes together are similar to the query "Design View" in Access.  The big difference is that the columns in the Criteria pane are listed vertically on the left as opposed to horizontally across the top.

SSMS Icon via Free Vector Sql Server

All original code samples by Mike Wolfe are licensed under CC BY 4.0