Insert

Video Transcription

Welcome back everyone. In this video, we’re going to start talking about data modification queries in SQL. So data modification, if remember, is one of two major parts of the SQL language. So we have data manipulation language, or DML, and Data Definition Language, or DDL. So far, we’ve been focusing on learning the SELECT clause and all of the other statements that go along with it. That is one of the six statements that we have in the data manipulation language. So DML is going to allow also insertion So inserting data, updating data that is already exists, and deleting data that is in our tables. And we can also combine a number of those statements together using a MERGE statement, which we’ll also be covering. But first off, let’s talk about insertion. So inserting data is a pretty flexible statement inside SQL and SQL server two to go along with that. But for now, we’re going to focus on just the standard insert statements. So insert values, insert, select, and insert Exec. And we’ll show an example of each of these. But these are also standard statements.

So if you go off to use MySQL or Postgres or any other SQL based relational database engines, these statements should be roughly the same as you are experiencing in Microsoft SQL Server. So the insertion syntax all begins with insert. So insert is the primary clause here, optional you can include into, so insert into and then the table name. And then inside of the parentheses, you can list out the columns of the data for the data that you’re actually inserting. So you can exclude or omit a column from a table and during the insertion statements, if that is the case, there are a couple different situations here, the first situation is going to be that there is some constraint on that column that allows it to be omitted. So that means it’s either going to have a default constraint, meaning it’s going to have a default value assigned if that column is omitted from my insertion statement, or there is a null constraint allowing that column to be nullable. So if no data is actually included for that column, then no is placed in that column instead, for that row or for that record. Otherwise, the insert statement will actually be rejected. Because if a column is non nullable, meaning that you can’t have you can’t have missing data, or there is not a default value associated with it. So both of those things are not true, then we fail our Insert because the the constraints actually are not validated.

But let’s take a look at a few examples of what this looks like inside of SQL. Now we’ve, I’ve shown briefly in some past videos, what insertion looks like, but we really never talked a lot about insert in detail. So the first thing that I need to do here is actually create a toy database here for us to play with. I’ve used this in the past for some other examples. So we are go using the person table, or creating a person table has Person ID as the primary key, not nullable. And this is an identity column. And we’ll talk more about identity here. What that means for insertion, first name, middle initial last name created on and then we have a unique constraints. So last name, first name together must be unique. And then we also have some non nullable columns and some nullable columns, as well as some default constraints here as well. So this is this, in general is the simple form of our person table. Now, this works out, okay, this is perfectly acceptable SQL creation of the table itself. But typically, I was gonna I will push more towards this structure here, which is an explicit naming of the constraints or each of the listed one. So we don’t have to name the actual identity constraint or the NOT NULL or knowable constraint. But it is good practice for us to name default constraints. And then it’s also very good for us to name our keys. And then so primary keys foreign keys and unique key constraints. Those are listed here, you can kind of see my naming convention. So it is the type of constraint. So PK primary key or default or unique key, and then the schema and then the table. And then the column. And this is really important primarily because if you don’t actually name your constraint, it is a very long winded, automatically generated name that SQL server creates. And it’s not very human readable.

So it’s very good practice to actually explicitly name those constraints here. So let’s go ahead and give this a quick run. There we go. So we have our table. Now actually go ahead and delete the simple version for now. And let’s do this insertion here. So, total execution time one row affected. So things work out fine for our insert. But let’s see what is actually inserted what that record looks like now. So if we run this, again, this is our data that we get back. So Person ID one, John No. Doe for the last name, and then created on. So this works out, okay. And if we examine our data a little bit closer, we can actually see why this statement works. Because we only include the first and last name as part of our insertion clause, right? We have, but then we also have Person ID. But we don’t have to include that because that’s an identity column. So that’s automatically generated, and sequence as data is inserted into our table. First name is a required column because it’s not null, and it does not have a default constraint. Same thing with last name, middle name is optional, because it is knowable, and then created en is also something that we don’t have to include, because it is also a not know, but it has a default constraint. And then we also, of course, have our unique key constraints down here as well. But what happens if we try to do this? So what happens if we try to insert something that doesn’t adhere to our constraints, so it says cannot insert the value null into column first name, table, so on so forth, column does not allow Knowles insert fails. So you will get this insert fails error if the data that you are trying to insert doesn’t actually conform to the constraints in that particular table. So that is important to remember. So what happens if we try to do some other invalid insert, here, so now I’m actually going to insert Person ID.

So if we sorry, if we run this now, says person cannot insert explicit value for identity column and table person when identity insert is set to be odd. So way back, and in some previous videos, we talked about the identity column, as well as sequence objects. But there is a setting in SQL Server that you can set on the backside to enable insertion for identity columns. Typically, that is not recommended, because identity is a self numbering column. So self number, self numbering database objects. So if you can get some unintended behaviors, and issues if you insert values for that, for that identity column or sequence object, even though you can definitely not recommend it. So let’s also look at this insert here. So I am now adding first name, middle initial last name, and then created on so what happens when we try to insert for default constraint? Everything works fine. And if we actually take this down here and see what the data is, we see that Ah, yep. So I have Jane here that got inserted, what the exact timestamp here of 112 1000. So default constraints are easily overridable compared to the identity column or sequence objects. So that’s something to keep in mind as we do our insertion, we could also insert more than one value at a time. So this process would look, take out this select. Here. So I’m going to show you so so far, we’ve just been insert values with one, one tuple. So one row. So this is all one row, if you want to insert more than one row with the insert values clause is just a, the tuple. So the row of data separated by commas, so the row of data comma, so this is first row, second row, third row that I’m actually inserting in here. And so if we give this clause a run, we can see now we have the two, the two rows that we had before plus, Joe, Fred and Marie, that we’ve just inserted with that one INSERT statement. So this is going to be something that is very powerful, very useful, especially when you start initializing your tables with data inside of them.

But there are, of course, all sorts of practical applications for being able to insert more than one thing at a time. And it’s typically actually going to be more efficient to if you have more than one thing to insert, it’s more efficient to do that in one single clause versus two or more, because each subsequent SQL clause that you actually execute a query that you execute, requires that new connection or a connection to the database. And so if you are reconnecting to the database every single time you run a query, and that becomes an expensive process to actually run, versus just combining everything down into one query. So there is definitely some overhead, definitely some overhead if we are going to try to insert more than one thing at a time through multiple queries. So another type of insert that we have or showcase here, what we just did, is here. So basically, showing this here just to show you what is actually being inserted in the values clause. It’s basically just kind of like a table. So you’re inserting this small subsection into your database. Okay, so that is the insert values clause. Let’s take a look at what the insert SELECT clause looks like. Here is an example. So insert, select, and let’s actually showcase what that looks like. And here we are. There we go. So over here, here is the additional three rows, or additional row right here that are right here that we’ve actually inserted. Beforehand, we had five rows, so we ended on Murray. And we just essentially just duplicated, all three of those, right, we just duplicated all all our all all five people that we had inside of our inside of our table. So what what really happened here is that our insertion is inserting the the result of this selection clause.

So if we run just that portion, if we insert it again, this is what that would actually look like. And we’re actually if you notice here, I’m modifying that last name to be XYZ the second. So we don’t have any issues with our unique key constraints. What the first name last name being unique. If I took this out, you would see see that error come out from that constraint. But this is also a very powerful way to insert new data into an existing table, in particular for inserting or duplicating records or taking things from one table and moving them to another. That is also a common use case for this select this type of SELECT statement. So and that query, by the way, so the insert select can be quite literally any SELECT clause. So let’s go ahead and create a new table Go here, customer order accounts. And then I’m going to use a CTE here. Right? Because a CTE is nothing more than essentially a table construct, right? It’s just a logical virtual representation of some, some query. So we have this CTE here, that’s customer ID, order, year order, month, order count, sales, etc. I’m pulling data from the wide world importers database. And then I am going to run an INSERT statement on customer order accounts. So essentially, again, here’s my common use case, here, I’m copying data from one table or one database into another. And so I can literally have any to any SELECT clause will be valid syntax or a valid statement for this, for this query, or for this type of insert will be run this. Here we go. Here is all of our answers. So here is the first first statement, this is the insert clause. So this is the number of rows that were just inserted. And then here are the number of rows that I was able to retrieve from my new my new table here. So that now covers the insert values, and then insert, select, so insert values being brand new data that doesn’t exist anywhere in your tables, or any other database. So you need to actually explicitly define those values that you need to insert. And then we have the insert, select, which is very useful for moving data from one table or one database to another. And then we also have the insert exec insert statements.

So I’m going to do a new table here, I’m going to redo the customer order counts. So similar that we’ve just had before, go ahead and run that. And then I’m going to create a stored procedure. And we haven’t, we haven’t actually covered stored procedures yet. But I will show you what this looks like. And there’ll be a follow on video here very soon. That covers stored procedures and in more detail. But what you can think a stored procedure as as a query, like a query like function that is physically stored, or sorry, the the the query itself is stored on your in your database, it’s still a logical construct. So like the data itself is not physically stored. But the the query itself is stored in it does provide some performance benefits over for other over other things like views and things like that. But again, we’ll talk about more detail about what a stored procedure is and how we can create them, and a nother video. But for now, just know that we can create a stored procedure. So think of it as a query that we can just essentially save and our database, and we can execute a stored procedure by using the exec exec statement. So exec, and then this is my stored procedures that demo dot retrieve customer order counts. So I’m executing, you know, think of this as a function that returns return data. So we run this, and here is the result of my stored procedure. But we can also add that as a input to our Insert class. So instead of this just being a SQL query, this can also be the result of a stored procedure that can also insert the result of a stored procedure. So if we run this, all of those rows that we had just pulled from our stored procedure, which again, this was what this was the SELECT clause that I had in my CTE just a little bit ago. Then we can feed that directly into the insert clause through a stored procedure. So this is again, very useful for pulling data from another database another table into a nother table right into another table. So transferring of information back and forth. But that is going to cover our pace forums of the insert clause. So the insert values, the insert, select and insert exact so insert values being the new data that’s that you’re creating to be inserted into the database. Then we have Insert insert selects, which is the result of any SELECT query, and then insert exec which is similar to the Insert select but we’re executing some stored procedure instead of executing a straight query but and follow up videos we’ll talk about other forms of inserting data into our database.