Hey guys, in this lesson we will be looking at inserting records into our tables. So to begin this exercise, we will use a script and we will look at the syntax to insert records into our student table. Now do recall that any table should be a collection of rows are records relating to the entity as described by the tables name. So the table students should only be storing data on students. So each record needs to be representative of students. In building our table, we would have stated or columns and the minimum data needed to build a report on a student and by the minimum data required.

I mean, what did we set to be nullable and not plausible. So we did specify that only the enrollment date was nullable healing, meaning we could always add a student's without specifying and enrolling date. So let's look at the syntax to insert a row of data. So the key word is literally insert into. All right. But then before we do all of this, we should always use the database.

So we use school underscore dB. And then we continue with our syntax. So we insert into the table, and in this case, or to his students. And then what we do is list the columns. And they are wrapped inside of parentheses. And then we specify the values.

And we also wrap those inside parentheses. All right, so I'm just writing it like this. So we see exactly what so this is the format for any insert, statement, insert into the table name, and then the columns listed and then the key word values and then the corresponding values with you. columns. So the columns as I have here would be last name. Comma, and each one is comma separated first name, comma, date of birth, and then comma, enrollment date.

Right, so these are the columns in which into which we want to insert some amount of data. So we can actually break the line so that we can see everything happening at once. So insert into the table, the columns values, and then we specify the values now the order that we specify the values must correspond with the columns. So in other words, whatever value I put here first, and remember that last name is a string value. So whenever we're going to be writing out string value or a word who wants to use quotation marks are single quotation marks, the double quotes can work also, but I prefer to use the single quotes first. Alright, and then we see, let's say for instance, I'm going to insert test as the last name, and the first name would be student.

And then the date of birth and date of birth or date, data type could also be in a string, and most commonly accepted formats would be here as in yy, yy dash models as an M M dash D, D, D. So it would be a four digit year, dash, a two digit month, two digit day, and that is almost acceptable in almost any database management engine that you would be using. And then for the enrollment date, let's say for argument's This student is not enrolled as yet, but we want them on record. So I just put no. So whenever we have another field, we can actually just say no. And even if we omitted this row this column from the insertion, then it would just get another value by default. Also, it's worthy to note that if you have four columns here, then you should have four corresponding values.

So there should be no mismatch between the number of columns outlined and the number of values all tied, if you have a mismatch, then you get an error. All right, so that is how you would go about inserting one rule of data into the database and of course, we end with a semicolon. And then if I execute, then you will see that it was successful, according to this log down here in the output, and one row was affected. So if I were to look in the students table, I should see at least one student record. Now this can get tedious if you Have 567 students, our 1020 students to enter. And it would be get tedious trying to change all these values with each student record.

So SQL actually gives us a mechanism by which we can actually insert more than one. So I'm just going to copy this code and paste it down here. And then I'm going to highlight that you can specify the columns one time. And then you can have multiple sets of values going into those columns. So using sorry, Xin control and the letter D, as in dog, you can actually duplicate each line see that I'm just duplicating. And I'm just showing you that you can actually have values and you can have multiple sets of values being inserted at once.

So this is how you do a multiple row insert. And then all you have to do is just make sure that each one is separated by a comma, a bunch of the last one which we know does not If como boats would get more like a semicolon to signify the end of the file. So I'm just going to go ahead and refine these values a bit. Alright, so I changed the numbering on the names here. So I just have students one, test one through eight. And oh, I didn't modify the student numbers.

And there we go. So that's better. So we have student, test one through student test it, and I changed up the date of birth. And then you notice that for enrollment data, put in some dates, and left some notes. So the fact is that as many rules as you need to insert, you can actually write one statement that will execute them all. All right.

Now, you also may have noticed that I didn't include an ID column here. And that's because once again, when we were creating our database, we specify that Id should be auto incrementing. So we wouldn't be specifying a data value for ID IDs taking care of itself. So once we insert a record, Id will automatically count up to have a value to uniquely identify that record that is being inserted in that command. so in this situation where we're inserting eight, that's a threat. It's real rolls.

When I execute this, then Id will automatically increment for each of those rules. And to run this statement by itself, because I have this statement that I ran before, but I don't want to run this one. Again, I just want to run this portion of the script, then, like we discussed in our previous video, we can just highlight that text and then we click the lightning bolts that has the cursor up affixed to it. And once we do that, it will only execute the portion of the code that is highlighted. And then here we see that we have the answer was affected, DNS records being inserted with no duplicates and no warning. So that was very successful.

And that is essentially how you go about inserting data into your data table. So I'm going to save this script. Alright, and I added some comments to it. So at least you'll have that for friends that appreciate exactly what's going on. And once again, we could have written all of this in one line as depicted by this template here or even for the multiple insert as long as you comma separates the section with the values up until the semi colon at the end, but we can also break the line and the blue dots will keep us in check as to which statement belongs to which part. So that's it for inserting records using the scripts.

Next we look at how we insert records using the workbench interface.