Database Fundamentals #14: Modifying Data Through SSMS

I’ve said it before, but I feel I should repeat myself. Using the SSMS GUI for data entry and data manipulation is not the preferred mechanism. T-SQL is the right way to manipulate the data in your database. For purposes of completion though, I will show the GUI methods in this blog series.

Information doesn’t go into the database and stay there, unchanged, forever. Data is modified. This occurs because information changes, such as when a person marries and changes their name, or information was incorrectly entered, in which case you need to fix it, or just about anything else. You have to have a mechanism for modifying existing information.

Modifying Data

You start modifying data in the tables the same way you did the insert, by taking advantage of the functionality offered by the Query Designer Interface. Open this window the same way as you did before, right click on the Personnel.Person table and select “Edit Top 200 Rows” from the context menu.

You can see that the PersonID values have been populated automatically. You can also see that the CreateDate values that were using GETDATE() to populate the date and time have a time value that was automatically filled in. Since you can’t see all the column by default, you can move your mouse over to the column border, when it changes to two arrows pointing in different directions, you can click and drag to show the different time values that were previously partially masked.

To edit a value, just highlight the one you’re interested in. For example, to change the DateOfBirth for the row of PersonID 6, you can select the whole date value or a part of it and type in your replacement value. Let’s change the year from 1972 to 1969. If you hit tab like you did before, you’ll see the exclamation point appear for that one column. Changing the focus another row, either by tabbing until focus shifts back to the PersonID column on the next row, or by clicking on a different row will commit the changes you typed to the database.

Just as before, you can’t edit any of the values to store information that is contrary to the datatype for that column. If you do, you will generate an error when you attempt to move away from the edited column.

For the PersonID with a value of 5, change the year of birth from 1980 to 1981. Then look down at the bottom of the screen. You’ll see something that looks a little bit like a video control. If you’ve edited the data, you’ll also see the text, “Cell is Modified.” Just like here:

You can use these buttons to control the focus of the grid. Working from left to right, the first icon moves your cursor to the first cell. The next icon moves it to the previous cell in line from where it is currently. After that is a text box with a value in it. You can put a number into this text box to immediately navigate to the row defined by that number. You just have to hit the Enter key after you type the number. The focus will change to the row you defined and it will be in the equivalent cell in the new row. The next button will move a cell to the right, or to the next cell, depending on how you think about it. The next button will move you to the last cell. The button right after that, with the little gold star, will move you to a new row. The final button isn’t covered here. Finally, you can see the status of the cell you’re in, if that status has changed .

This toolbar can help to make the use of the GUI a little bit easier, if you’re uncomfortable using the tab key or the mouse to navigate around.

Conclusion

This is how you can use the SSMS GUI to manipulate data within SQL Server. However, the real strength of a system like this is the ability to automate functionality. You have no automation ability using the GUI. You have much less control. Overall, there are many reasons why you should avoid using this. Again, I’m showing you the functionality so you know it’s there. If nothing else, when you get support calls from someone who has gone down this bad path, you’ll have a little awareness so that you can help back them out.