Categories: SQL Server

Quickly Editing Data in SSMS

WARNING: do not do this in production.

A quick blog post on a SSMS trick: how to quickly edit data. Sometimes you directly want to manipulate data without having to write a SQL statement. For example, in the development environment you want to modify a value in a column to test out a scenario, or you are creating a table with some dummy data.

In SSMS, you can do this by right-clicking the table and selecting “Edit top 200 rows”.

You’ll be taken to a grid where you can modify the value of each cell.

But you only have access to 200 rows. What if you can’t find the row you want to modify? Luckily, you can adapt the SQL query that fetches those 200 rows. In the toolbar, you have the following buttons (screenshot taken with SSMS 18.4):

When you click the left button, you’ll see a diagram of the table:

Just like in the graphical query and view designer (please do not ever use this, it creates abominations of queries). But this view let’s you easily deselect columns to get a less cluttered view. If you click the second button, you get the full query designer:

Aside from deselecting columns, you can now also filter and sort columns. But what I’m really after is the little SQL button, which will just show you the actual query:

You can edit the query to suit your needs. You can remove the top 200 clause, sort the data, remove columns, you name it. One disadvantage though: there’s no autocomplete, no intellisense, nothing. Even if you have a tool like Redgate SQLPrompt installed, it will not work in this designer. An alternative is to write the query in a query window and then copy paste it into the designer. But once you run the query (F5, Alt-X or whatever your favorite shortcut is), the editor will re-format it.

For example, if we have this query:

SELECT
     [CustomerKey]
    ,[GeographyKey]
    ,[CustomerAlternateKey]
    ,[Title]
    ,[FirstName]
    ,[MiddleName]
    ,[LastName]
    ,[NameStyle]
    ,[BirthDate]
    ,[MaritalStatus]
FROM dbo.[DimCustomer]
WHERE [FirstName] = 'Lauren'
ORDER BY CustomerAlternateKey;

It will result in the following:

Now we can easily find and modify the row we need. Remember, once you change a value in the grid and select another row, the change is auto-committed. If you’re still in the current row, you can use escape to reverse your change. As long as the row is not committed, there will be a little red icon:

Entering new data can be done using the last “empty” line, with all the NULL values:

If you have an IDENTITY column, the next value is reserved once you start typing in this row. If you rollback or an error occurs, you will have a gap in your identity values.

Finally, if you cannot find the buttons in the toolbar to get the designer (which is actually the motivation behind this blog post since I couldn’t find them earlier :), you can right-click anywhere in the grid, choose pane and then SQL.

As you can see, there are also some new shortcuts for you to memorize 🙂


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

View Comments

  • Do you know a way get to this sql editor before there's a grid? e.g. I often want to start with a sql query to get the editor, but instead my workflow is to click around with the mouse, right click an arbitrary table, select edit, press ctrl-3, then replace the query with what I actually wanted. Is there a way to get this query editor without all the mouse clicking?

    • Hi Matt,
      not that I'm aware of. To be honest, I haven't spent much time looking for it. But you describe a very good use case. Maybe you can add it as an idea to uservoice?

  • Koen
    I know this is 2 years ago but this article is great! I have been wondering how to do this for a while. Thank you.

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago