Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Database Fundamentals #12: Adding Data With SSMS GUI

DZone's Guide to

Database Fundamentals #12: Adding Data With SSMS GUI

The SSMS GUI does supply a mechanism for adding data, but it's not adequate. It's better to use T-SQL. Still, it's good to know how adding data with the SSMS GUI works.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

In the previous Database Fundamentals, I argued that you should be learning T-SQL — yet the very next post I'm showing you how to use the GUI. What's up?

Why the GUI?

It's a very simple reason. I want to show you what it is so that I'm not hiding things. However, showing it to you will quickly expose the weaknesses inherent in using the SSMS GUI for direct data manipulation. It's a poor choice. However, we'll understand how it works at the end of this post. I'll also cover it in other posts, showing how to UPDATE and DELETE data using the GUI. They will further illustrate the weaknesses. You will, however, know how it works.

Data Entry Through the GUI

If you are not already connected to the server, do so now. Navigate in the Object Explorer window until you have the Tables folder open. Right-click on a table. From the context menu, select the menu choice Edit Top 200 Rows. This will open a window known as the Query Designer User Interface. Strictly speaking, this is not a window for editing data. Instead, it's a window that has been put together to assist you in building your own T-SQL queries. Keep that in mind as you explore this window.

If no defaults have changed and you have not experimented with entering data, then you should see a blank grid something like an Excel spread sheet.

There is no data in the table. If there had been, you'd see up to 200 rows on display in this window. There are a number of other functions associated with this window, but we won't be covering them all here. For now, we'll just use this window to add data to the database. It's already prepared a row for you. All you have to do is click inside the grid and start typing. If the first column that you start trying to type in is the PersonID column, you might notice that you can't type into it at all. This is because I identified the PersonID column as an identity column when the table was created. This will be filled automatically by SQL Server. Click on the next column using your mouse pointer or use the Tab key to change the focus to that next column.

This column I can type into. I can type in a value for the FirstName column, then hit Tab again. When I do this, a red exclamation point appears. This is because I've modified the data in that column. If you hover your mouse pointer over the exclamation point, an informational window will appear that looks a lot like this:

As I move through the grid adding data, the same thing will happen. In the CreateDate column, if I were to type in the value mydate instead of a valid DATETIME value and then hit the Tab button again, I'll immediately get an error because you can't enter data into that column that SQL Server doesn't recognize as a DATETIME value. The error message looks like this:

Click on the OK button and the focus will return to the CreateDate column. I can fill in the rest of the row until I hit the last column. If I hit Tab from there, I'll see the exclamation points disappear. And where there was no value in the PersonID column — assuming we haven't entered other information — there will be a value of 1. This is the automatic identity value at work. This row has been added to the database. You didn't have to save it or anything. It all occurred automatically.

If I check the value in the CreateDate column, because it's a DATETIME column, SQL Server assumed I meant for the time to be midnight for the date I entered. This is part of the difficulties of working with the GUI. Also, any foreign key values I'm going to have to go and find.

Conclusion

The SSMS GUI does supply a mechanism for adding data, but it's not adequate. A better way to do it is with T-SQL. That's the next Database Fundamentals blog post.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,t-sql ,ssms ,gui ,tutorial ,data manipulation

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

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}