Hey guys, in this video we will start inserting some records into our database tables. And today our database table of choice is students. So we are going to create a few student records. And we look at using an SQL script to do so as well as how we can use the editor to accomplish this task. Now there are a few things you have to do before you can just start writing your script and one of them is to select the database that we're about to use our targets for or script. So once again, to select that database we have to use the use keyword and we specify the database by name.

So in this case, we want school and then we go about creating our script. Now the keywords involved in entering our record or inserting a record are literally their their standard industry and truly so insert And then we say into, and then we're expected to see what database table and then what columns we're inserting into. So do recall when we created our table, we created it with a few columns. And we can always just drill down on the in the Object Explorer, and expand columns and see and refresh yourself. And you can see insert into and the table name is students. And sometimes you'll actually see people write out the entire name dbo dot students.

So dbo is just a keyword that is within the context of Microsoft SQL Server. And it's it sits in front of more stable so even if you've been putting it there, chances are you will see it appearing there at the default settings. Alright, so you can always just say insert into but I'll leave off the dbo for now. And the table name and then you open a parenthesis Sorry, you don't need to space it there and close. And then in these parentheses, you actually specify what columns you're going to be entering data into. Now this is important because remember, when we were configuring our tables, we set up one of the columns, which we called ID to be our primary key, and auto incrementing.

That means when we're inserting a record, we don't have to put anything in here we have to specify other columns like last name, first name, date of birth and enrollment date. And what we cannot put or shouldn't be attempting to put anything here so we need to specify also do recall that one of these was allowed to be no which was enrollment date. So I can just hover over and you will see or I can just expand the Object Explorer. And you'll see here last name, not no first name, not no date of birth, not know about enrollment. It could be no, which means I could specify up to three columns, I don't have to specify all four of these. So I'm just showing you that this is why we specify the columns, because not ever record me and get every bit of data.

Of course, if the data point is not nullable, then it will not execute the script because I can't be putting in a student record, I can't be inserting something into the students table without one of these mandatory data points. Alright, so let's work through this. So the first one is last name. And I know some people don't like to type so you can actually just drag and drop. And then you'll see that the SQL editor is actually put in these square brackets. So that's also a feature that you would notice when SQL Management Studio generates some of the SQL for you.

They usually put these in square brackets and as we go on explaining And what those square brackets are for. So we can just mix and match, and we just drag and drop our cross. And I'll fill this entire record with all the data points. So we're inserting and use, we're inserting into the students table into the columns, last name, first name, date of birth enrollment date, and then you see that the red line is there. So that means the script is still not quite finished, because we need to specify what values will go into each column. So we have to say values.

And I just break the line and bring that down. And then we open and close parentheses again. In these fantasies, we have to make sure that the values that we insert here, line up with the columns that were ordered here. So the first value has to be the last name and sorry in SQL I find it easier to just use the single quotes for string. Alright, I use double quotes and that was an accident use single quotation marks when using SQL, Microsoft SQL at least. So last name, and I will insert Reynolds first name.

So rennels lines up with last name, the next value should line up with first name, ie date of birth. The date, time format that is default in SQL Server is year, month day and that comes in the yy, yy, dash m m dash E, D. If you're familiar with Excel, then you're familiar with these data formats. If not, that's fine when entering a date in SQL into a column that is a date, time or even Once it's a date, type, the expected for months should be the year so we're in the year will disperse was born in 1988 the first month and the 15th day. So once again that's year, month date, that is the expectation. And for enrollment dates, we will enter 2019 dash 05 dash 01. So that is the first of me 2019 and then we can review this and ensure that all the values are correct and correctly lined up.

Once again, these values have to correspond with the order that the columns appear in. And then we can go ahead and execute. And once we execute we know it is successful and it tells us One rule affected. That means whatever it is we did, our role was somehow affected or that it was created. In other examples, you'll see where we can manipulate the data otherwise. So this is an indicator that the script was successful in manipulating that rule.

So let us try and insert our loader record. So let's read the script again from scratch. So once again, we say insert into, and then we choose a table name students. And then he open and close parentheses, and then we list all the columns somewhere to mix up the order of the columns a bit. I'm going to say first name first. Then last name, then date of birth.

As a matter of fact, I'm going to do enrollment date. So I want you to see the arrows that you would get when you don't follow up. The rules that you set for the columns, so values, and then in the values listing, we have to make sure that Firstly, we have the first name, we can see Jody McIntosh, and these are purely fictional names and enrollment dates. I'll just say no. All right, because we can pass in a null value. And then I'm going to execute this the expectation is that we should get an arrow because we did say that date of birth is mandatory.

It's where we're trying to insert a new students without a date of birth value. So if I say execute, then we see cannot insert value node into that column. So we do get an error message fails. So we know we need to rectify our scripts to reflect our rules. So I have to say date of birth. And I have to add a value.

So 1990 for the third month, the is the year, month date. And then we execute and then we'll see Wallah one rule affected that just make one more adjustments to this type of script. So I'm just going to instead of taking everything else, I'm just going to erase the values and I'm also not going to insert the enrollment dates. So first name, last name, date of birth, do recall that enrollment date is nullable. So it is not a mandatory field. So this time, we want trishul Williamson and her date of birth is the year 1995.

This eighth month And then we click Execute. And there you have it. So this record went into the database without any enrollment date. So at this point, we would have at least three records in our database. Now you're probably thinking this is tedious every time I have somebody to insert, I'm going to have to erase or, you know, rearrange or something. But then there is a way to actually do a multiple insert using one insert into statements.

And that's what we're about to do. So when we're going to be inserting multiple records, in one statements, what we do is we maintain this first line the insert into table name, and then we spell out all of the columns. And let's say all of these next few records have an enrollment date. So what we do is write the first one test. I'm just going to get very short handed no Students, and this person was born 2001 month if they and you can leave out your dashes that don't use slashes, it may be forgiving sometimes when you mix up your day for months, but ultimately, that's the date format it expects, so you can just stick to that and the enrollment deeds is low. Now when I want multiple records in one statement, what I'll do is just write comma, and then go to the next line and write another one of these.

So I'm just going to take this line and copy and paste and this person is tests, test one, student one, and leave the same date of birth. Or let me just change it so that we can see the difference in the data. And then as many records as you will have, you just write a comma, and you can just duplicate that line. And what I just did to duplicate is another keyboard shortcuts I held on on Ctrl and C n V, which is copy and paste, but then without highlighting the line, it just duplicates with whatever line the cursor is on. So that's another neat trick that you can try out. So I can just Ctrl C V, and put in as many as possible, right, each one comma separated up until the very last one in the series.

And, of course, the editor will make no mistake in pointing it out to you that this is invalid. So I'm just going to go ahead and fix up these records a bit. And we will then see how this works. All right, so I've made some adjustments to this script, we are inserting into the table and I just put some differences in the data so that we can move ahead and not be confused as to any repeating data so that you can see where each record is unique in its own right and left some of the enrollment data isn't all put in some and I'm just showing you that as many records as you have, you can use this just use that technique and stringing them along. And then we will execute one time. And then we can see that we move from the usual water rule affected to seven rules affected, because we inserted 1234567 new students.

So as many records as you put in once it does not fail, you will see a message like this indicating that you have done something, right. No, I know that we've been inserting, inserting, inserting, and you're probably wondering, you know, is this a con I'm not seeing the data, I'm just seeing a sentence that says rows affected, I want to see the data and that's fine. We do have another video coming up where we start extracting data. But then Until then, I'm going to show you a very quick way to view the data, and also how we use the editor to replicate this kind of functionality. So I've added some comments to this script, and I'm going to show you how we can save a script file. So you've written a script.

Reply this may be you were tasked with writing the script to actually put in all the student records and then you have to submit it to your database administrator or whoever is going to actually pull the trigger, and you need to have this file exportable, you can actually just save the file Ctrl S or go to File and click Save. And then that will prompt you as to where you want to put the file. So I'm just going to put this one on the desktop, and I'll see insert students, and the default file extension for any SQL file is dot SQL. So I can just save that. And then you will be able to come back or open this and executed on another computer or in another setting where you have a school database, database name school, and all of the data needs to go into those tables.

So that is how you go about exporting your scripts and saving them for future reference. Maybe you're working on it that you needed for that Laser. This is how it's done.