Hey guys, in this video we will be looking at using the my SQL workbench interface to insert records into our data tables. So in our previous video, we would have looked at how to do it with a script, where we specify the database to be used. And then we wrote our keywords and our statements. And we looked at how we can insert one at a time versus many at the time. And you may be looking for a quicker and potentially easier way to do this without having to come in and write up a whole script. There are advantages to the script usage because then you can save the file and you can use it at a later date on maybe even another machine.

So that's the advantage to using the script file as its exportable. But then workbench also gives us a way to insert records directly without having to write a line of SQL code and to do that We can just hover on our table. And in this case, we've been interacting with the students table. So we can just hover on the students table. And then you'll see the three icons appearing. And the last one has like a lightning bolt on the table looking icon.

So we can click that. And we see that it executes some command that says select from the students. So you'll see that select query No, in a future lecture will break down that SELECT statement. But for know, that query will give you this result grid, which is what workbench uses to help you insert more records. So here, they're actually listing out all the records that we would have created. And you see that, like I said, In the previous video, for each record that was inserted, we get a unique ID.

All right, so it's calling things so that means the next one will be 10. And we see all of the data that we would have put in so if we wanted to create another student at this point, then we could actually just click in this row that has all values and just start filling in data. So I can see Williams and I can add a record for myself. I'm not going to tell you when I was born. All right, and I this was a typo, but I'm going to leave that because I wanted to see how my SQL handles erroneous records. So I'll just leave this one as No.

And then I press enter. So what happens is that when you press enter, it just goes on to the next one. So you can add as many as you need. All right, and then at the end of that process, you have to click Apply. And then apply is where we'll actually still generate code very similar to what we just wrote, which is just insert into and then you'll see that it kind of fully qualifies the database name where it says school db.com Students, because the reality is that if you don't have the use statement, then you can actually specify the database dot the table. And I kind of removes the need to have the use statements, because then you know exactly which table in which database is being targeted.

Alright. And then if we look at the rest of the code, it looks just like what we wrote all right values, and then it puts in the values including that invalid date. And then if I click Apply, then you'll see that it gets back with an error saying that an incorrect date value was tempted to be inserted. Alright, so so SQL, my SQL is actually doing its own validations on the data based on the data type that you specified. There is no 200 and first month, so that is just an error. So it knows that it should be year, month day, so of course it's going to validate before it attempts to put that in and that's it.

One of the principles of data integrity because data integrity speaks to having data that matches the needs and matches the standards set up by the designer of the database. All right, so I can just cancel that and make that part on. And I'll just add a few more rows. All right, so I've added a total of three rows with fairly consistent data standards or are in keeping with the data standards, not violating the integrity that we're looking for. And then I can just click Apply. And then you see that instead of doing the multiple insert is actually generating three different insert into statements.

So the thing with the multiple inserts is that if one of them fails, then the whole batch fields as opposed to doing one by one so that it will run this and then if this one fails, then it will probably just stop the execution of the script there, allow you to correct this one and then it will move on. So it That's the difference between it. So that's probably why they did it in three different statements to give each insertion command a fair chance as opposed to clumping them in one statement and feeling them all if one is bad, so we can just go ahead and click Apply. And we see here that there is still okay, I know what's happening. So this is actually a four digit year, and two digit month and then two digit day. So I have the 21st month there's no 21st one, I apologize.

So let me try that again. Apply. And I see my URL, I should not have put the value null, because null is kind of represented like this. So if I just leave it empty, then it will automatically know that this is a no. So let me try that again. Alright, so instead of having a lowercase low, then I'm going to have an upper case where no, which is actually a keyword.

And my skill is very particular about how it will enter it. So I'm just going to do that. Alright, so this is actually a good lesson. So if you want to leave something low, then it's best to just leave it. So my SQL represents it, because by me trying to type in the word know whether in lowercase or uppercase, or leaving it blank, we see that we're just getting some error here, and the operation is just going to fail. So I'm just going to remove this.

So to remove that, you can just click it, click the rule, and then right click and say delete rule and then it will remove it from that selection criteria. And then I'll just recreate it on here. Just so there's adequate preparation intuition. All right, and I'm not going to change that value that is there. And then I'm going to apply once more. And there we go, it was successfully executed.

And once we click finish, then we see that the number is incrementing. So you'll notice that it jumped all the way to 20. And that is because of all the failed attempts to insert, the ID was actually trying to auto increment each time. And then it just kind of associated any failure with a bad idea that's already used. So it just went to the next set of ID. So all the times I feel just No, he would have gone up by 10 1112 1313 1415, and 16 1718.

All of those failed. And until now it's at 2021 22 because now it was successful. And the next available number at the time was 20. So that's While these IDs would have jumped, so that is some behavior that you can expect from auto incrementing primary keys, and do remember that the next record will always get the next number and that is available as it comes up. So from this view, you actually have the option to insert records like this in a grid view, we can change records data right here, as long as you click apply at the end. You can also change the view, you can change the form editor that allows you to see it in a nice form view or fields under type so you can see all of the dates on the fields.

And you can also see query stats within this is for later when we start getting into some of the fun querying stuff in our select section, so essentially that's how you would go about inserting records into your table using My SQL workbench interface