DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Fundamentals #14: Modifying Data Through SSMS

Database Fundamentals #14: Modifying Data Through SSMS

Overall, there are many reasons why you should avoid using the SSMS GUI for data entry and data manipulation. I'm showing you the functionality so you know it's there.

Grant Fritchey user avatar by
Grant Fritchey
·
Oct. 17, 17 · Tutorial
Like (1)
Save
Tweet
Share
2.43K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database Data (computing) sql

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • SAST: How Code Analysis Tools Look for Security Flaws
  • How To Generate Code Coverage Report Using JaCoCo-Maven Plugin
  • The 31 Flavors of Data Lineage and Why Vanilla Doesn’t Cut It
  • Top Authentication Trends to Watch Out for in 2023

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: