Add data to a table

- [Instructor] To add data to a database table, we can use the Insert keyword. To add information, we need to tell the database a few important things. So, the Insert clause has a few components in addition to just Insert. We need to tell it what table to add data to, what field or fields to add information in, and then the values to put in those fields. Let's add a record to our People table and add the first name Bob. To do that, I'll write INSERT INTO people and then in parentheses the name of the field that I want to use, in this case, first name.

Then the VALUES keyword and again, in parentheses, the information that I want to put into that field. I'll run the statement and the database tells me that my query was executed successfully and that one row was affected. That's what I'd expect. I'm adding one record and so that would be one row. Let's take a look at the table with SELECT * FROM people.

And down here at the bottom of the results, there's record 1001, Bob. The first-name field has what we added and the rest of the fields have Null in them because we didn't set any information so they're empty, or have no value stored in them. Let's write another insert statement to add some more information. I'll write INSERT INTO people and this time, we'll use a couple different fields. In parentheses I'll add first name, last name, state, city and the shirt or hat field.

Then I'll write VALUES and we'll put values into each of those fields. These need to correspond with the same position as the field that they're going into. So, in the first position I need a first name and a last name. Then I need a state, a city, and a valid choice for the shirt or hat field. I'll choose hat. I'll run that and I'll write another query to take a look at the table again.

SELECT * FROM people. I'll paint that and run it and here below Bob is Mary Hamilton from Portland, Oregon who's a fan of hats. We can add more than one record at a time, too, either by running many insert statements at once or by giving the field names and then a series of information in parentheses. I'll change this insert statement to just have a few values, first name and last name.

And we'll put some different values in here. I'll add George White and then after this set of parentheses, I'll add a comma and add another set of parentheses. Here I'll add Jenn Smith and we'll add Carol Anderson as well.

I'll paint this statement with my mouse and run it. And now I see that the query executed successfully and that there were three rows affected. Let's take a look at the table again. And here they are beneath Mary Hamilton. Notice that we can selectively add information to fields. We don't necessarily need to have all of the information for our record. But that's not necessarily a good idea. Some of the fields in our table may be really important, so we want to make sure we maintain the quality of the data when we're adding records.

Some database software will help you out with this, especially in determining whether the data you're adding matches the correct type for a field. SQLite, as I'm using here in DB Browser, is a little bit lax about that so it won't give me an error if I try to put, say, a text string in a numeric field. But MySQL and most other databases will give you an error. Additionally, in most databases, once you make a change to the database that change is permanent. Here in DB Browser, though, we'll need to choose Write Changes in order to make this change happen on the database.