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.

Identity & Sequence Objects

Video Transcription

Welcome back everyone. In this video, we’re going to continue our discussion on data modification. And in particular, what happens when we use Insert on a table that has columns that are identity or sequence objects. So, in general, a lot of our tables are going to have an identity column or a sequence object, because a lot of what our keys are based off of our primary keys are usually going to be a surrogate key that is, generally speaking, auto numbered. And so that’s where the identity column or sequence objects are very beneficial to use for, in general, we do have three different possible ways to utilize the values coming out of the that identity function. And so we have the Add identity, and this is going to return whatever value was generated last, from the last query that we executed on that table. So if we inserted the first record in a table, and that identity, for that column was one at identity will actually pull the that one out. And we can utilize that as just like a variable inside of our queries. Likewise, we have scope identity, which returns the last value that was generated by that identity column by the last session in the current scope, and I’ll showcase an example of how this actually differs from the, the previous at identity.

And then we also have ident, current and then table name. And this returns the last identity value generated globally. And so you can either ident current without a table name, or specify a table name. So that can be specific, either global, or specific for a, a, a particular table. identity itself, remember, is non standard, but in general, is a simple, simple form to actually add an identity column to our database. And generally speaking, when we’re inserting, it’s as long as you remember those last couple of items, we can pull that value out of that identity column, if we need to do any advanced, advanced things to the data that we actually insert. But it is non standard, the standard SQL definition is going to use sequence objects which achieve a very similar result, sequence objects, remember, look something like this, I’ve showcased these before, we haven’t really used them a lot, so far and the queries that we’ve been executing, but a similar process, right, we have the start value, the smallest number that we want, and then and then the number that we actually increment from so this can be up by one or by two, or so on and so forth. And then we can indicate whether or not we want this window range. If we have a specific range of numbers, we can force it to cycle through. So if it ever hits a min or max, it’ll actually go back to where it started from. There are other options to achieve similar results to this. But generally speaking, they’re a little bit more advanced topics. And don’t come up as often or as in normal basic usage, and simple database designs. But you can do some more advanced things with Seekins objects when we’re working with inserting data into our tables. general syntax that we’ve covered so far, and again, I’m going to cover a little bit more into into more details on examples of the identity columns and things like that. But we have covered these three primary syntax of forms of our insert statements.

So insert values, insert, into Insert, select, and then insert exec and everything here. This is looks a little bit more complicated than it really is. This is just the full SQL Server Standard definition of the statements. But you can see the previous examples of what we have done so far, but this kind of shows you what different options you can actually include as part of your insert statements. But let’s take a look at some more examples of the insert clause. And in particular, how our insert is going to work when we’re dealing with identity columns. So this sequel is included in the Canvas module. So this is just going to be recreating all Have the tables that we for all the tables that we use as part of our demo schema. So person address, things like that. So nothing new that we haven’t seen yet there. But in particular, I want to go ahead and create and focus on particular, our identity columns. So if we pull this back up here, this is just going to highlight this identity of these identity constraints. So we have an identity constraint for Person ID and identity constraint for identity column for our customer. Both of these are primary keys, they start at one and increment by one each time. And again, in a different SQL language, other than Microsoft SQL Server, you might see a sequence object being used instead of identity. So let’s take a look at an example of inserting into our tables that have an identity column, where we want to actually manually add and add to the value for that identity column. Typically, when we want to pull a value particular for an identity column, it’s best practice to use scope identity, and rather than, rather than an identity, unless you need to ignore scope, and scope is typically going to refer to this the current session that you are executing in. So right now my my tab here, this is my connection to our database. And so this connection is our scope. And I’ll show an example of a different scope here in just a second. But let’s go ahead and execute this. And let’s go ahead and find our JD enterprises. We just inserted into our customer. And so our add to Person ID was linked to our contact person was linked to John Doe. And so this is a helpful use case for scope identity. And again, scope identity is going to be our typical preferred our preferred method two pulling out the last value for an identity column for for our insert statements, but we can also so if we so this was five, and then if I do a another connection here and execute this, you notice that my, the last identity now is six, whereas here, it was five.

And if I run another one here, this is still a different scope, but the identity, the last identity hasn’t changed yet. So it’s still going to be six. So this is a this is what the difference is going to be for that versus at identity, so we can actually showcase this. So our that at identity is going to be empty, right? Because there hasn’t been if we go back here there we go. If we go back to the slides here, remember the difference here. So this is an identity is returns last I didn’t the value generated in this session. Similar ish, similar thing was scope identity, but the session and current scope, and then ident current returns the last identity value value generated globally. So that’s the primary difference here, right? That’s the primary difference. So scope, so session is this connection, right session is this connection. Scope is going to be scope is going to be in the blocks of the batch that actually run in and then the session is going to be from a different connection to the database, right? So that’s why this here scope identity, even though it can include The value generated by the session in the current scope, there is no set there is this is a different session, right, this is a different session. And so the global last identity value was what is six. So that’s going to be the last item that we actually insert. So just some primary differences with what, what we actually see with scope, identity, and identity, and ident current, just some different behaviors there. So just be just be cognizant of when we need to actually use the value from an identity column like here, we’re inserting that into as a foreign key into another table, the you have three different options. Typically, you’re going to use this scope identity. But in some scenarios, you might want to use identity current. Or if you are using things in a different session, you may want to use an identity versus scope identity. So there’s some differences there. Okay, so that is the identity column, let’s showcase what this looks like with a sequence object. So let’s run this here. We need to also apologies. Demo dot person address, because we have a foreign key constraint that’s preventing us from doing so. There we go. So So now, what I’ve done here is I don’t have an identity property set on my person ID. So I’m actually creating a sequence object instead. And so to actually link that to our Insert clause. I’ll actually first let’s show let me showcase what that sequence object actually looks like. So I’ll actually run this underneath this tab here.

So run this and so here’s the next value out of our sequence object. So we can select that. So this is the demo dot Person ID sequence. And so select next value for demo dot Person ID sequence. So that gives us the next value that’s going to be used for the sequence object. And so typically, what we’re going to we can, we can actually do this before the insert if you’d like to. So you don’t actually have to, for First we don’t have to actually insert something into the table to get the value out of the sequence object. That’s, that’s number one. And two, rather than fetching the value, after we actually do the insert, query, you can do it before, so we can actually store this as part of a variable. So if we do this here, so we pull the next value four, so this is out of the sequence object, next value for the sequence, store that in a store that as new person Id give this run one row affected. And so if we pull that out, you can see here, our our next person that was inserted, so before, right, before, when we did every time, we actually execute this, this here, right, every time I run this, you see that my sequence goes up, because next value forces the sequence object to actually count to the next number. And so every time you execute this, the the sequence object actually increments the value regardless if you’ve inserted something into that table or not. So if I insert a another value, right, if I insert another thing into the person table and utilizing my variable here, which will be next value, that would be 23. The next person that I actually insert. So we are we don’t actually have to use a variable either here we can actually just put next value straight into our Insert query. So if we do this, insert into person, values next Next value for a demo personality sequence. Run that. And if we showcase this here we get 23, just like what we did just like what we saw before. So again, this is kind of the difference with between the identity column and the sequence object, the sequence object is disjoint, or separated from the data that’s being stored in the actual table. And that’s using that sequence object. And so you may get things like this, where even though I have only have two records here, doesn’t start at one, and doesn’t. And the second one is it number two, because I ran this statement here, next value for sequence multiple times before actually inserted the data there. You can also use it and multiple locations. So if we do something like this, so select Person ID, next value.

So as I mentioned, right, this is, doesn’t matter what table I’m pulling things from, I can use this in whatever I’m actually working with as part of my SQL queries, you can use the select clause and generate multiple values at once. So every time that next value in a column, if you use next value in a column that is generated every single time for every single row. So you know, things, things that we can achieve there, you can also reference it more than once. So if we do this, here, we have this so we have a person ID, P dot Person ID, P dot full name. And then and then this is from the wide world importers database, by the way, I’m pulling from the Alternate ID from our, from our sequence object in our demo schema. And then I’m using that same that value again, here for this column. So this is kind of a unique application here. So generally speaking, right? The, when you execute the statement, it’s all done at once, right? For every row, it’s all executed and pulled out at once. So the value used here, right, this statement here, both of these, this is executed all at once. And so the value that you see here is going to be the same value you see here, because we’re doing a row by row operation. And so the values for a row are executed all at once. And so the actual sequence number is the same for both columns. sequel is, is this see that number is used, the same or the number that is used as part of the sequence is the same for both columns. We can use sequences as default constraints as well. So so far, this is done manual. And as you can see our behavior and, and differentiation between things is kind of wonky, because we are our sequences, if we’re truly using our sequence only for our person ID and we want to sequentially you know, increasing as we insert records, doing it by hand can be a little bit tricky. It can be a little bit tricky. And you can, like I said, get a numbers that are kind of out of that are kind of weird. So if you run that next value multiple times you get something, skip a whole bunch of numbers. So if you don’t want to skip anything, we can actually force the sequence object to be part of our default constraint. And so this is going to act just like and I didn’t even call them, right.

So just like an identity, they call me now don’t have to define a value for that column when you insert data. And so we can execute a, let me scroll up here, we can execute this and get our next person ID. And so then if we wanted to also, you can also apply this as a window function. Right? So again, we can we can have it as a default value as part of our constraints here. Just like an identity column would be or we can also use this as part of a window function, which is pretty cool. So let’s run that. Here we go. So the same sequence over and then we’re, we’re separating our, our, we’re framing up our window and ordering it by full name. So if we run this here, so here is our window function being apply over our query here. So something that we can use, this is particularly used to define an order to our rows and our query. And we can also, if we want to, this is super handy. Something that is a little bit more flexible when compared to the identity column. But it’s very easy to restart our sequence. So if we force our sequence to restart, right, that becomes something a little bit easier to do. And if we sort, ascending here, you can see all of our sequence object values there. But if we sort by Person ID. That’s, that’s that. So just some different options that you can have when using sequence objects, particularly when inserting data into our database. So we have the identity columns, which are Microsoft, or T SQL specific. So Microsoft SQL Server, those are typically typically very easy to implement, because there’s a lot less that you have to do to actually attach that to a column. But we can also use anti standard syntax and use a sequence object, which is a lot more flexible. It’s not tied to necessarily one and only one table inside and one and only one column. And that sequence object can be used across multiple instances, multiple queries, and multiple different tables or even multiple databases at that as we shown. So generally speaking, a sequence object is going to be a little bit more flexible, and it is standard identity is going to be very quick and easy to use. If you’re using Microsoft SQL Server. I’ll leave into this video here on the general syntax that we’ve seen so far for the insert statement, but that will conclude our coverage on inserting data into your tables.

Delete & Truncate

Video Transcription

Welcome back everyone. In this video, we’re going to be starting our discussion into the Delete clause and our data modification series. So remember, our SQL language has two primary parts, the data manipulation language, which is what we’ve been working with primarily so far, and the data definition language, which involves things like the CREATE TABLE statement, we primarily worked with the SELECT clause for most of this course. But the SELECT clause is one of six statements that we may see in the data manipulation language, or DML. So what we’ve covered with so far with Dave, the DML, as far as data modification goes has been insert so far. And in this video, we’re going to be taking a look at the Delete functionality. And then later, we’ll start looking at how we might update data. And then also combine the Update, Delete and so on and together with into one clause using the MERGE statement. But first, let’s take a look at delete. So primarily in SQL, there are two ways you can delete data. The first one being the Delete clause, this is the most common way to delete information, it does support in T SQL, it does support the from clause as well. So delete from but that is non standard as far as the SQL language is concerned. So you may see different structures or different syntax and different database systems. So a T SQL or Microsoft SQL Server versus things like Postgres, or MySQL, the syntax may vary slightly. But delete is an atomic operation. Meaning that if your Delete clause is going to delete, let’s say five rows from Table A, then either all five rows get deleted, or none of them do. So the the delete operation cannot be interrupted. So it’s either all or nothing. As far as that is concerned. Later in this course, we’ll talk about transactions, which can help help support that animosity as far as the database statements go, but the other way you can delete data from a table and SQL is the truncate command, truncate is more destructive, of course, truncate is going to delete all rows from whatever you’re working with. And this is actually very minimally logged. So truncate is actually, if you were to remove all tables from a table, truncate would actually perform faster than the then the Delete clause, but truncate is, is quite destructive, right? You want to be careful when you use this command, because it does delete everything, it does delete all the rows.

And at least as far as the table is concerned, it’s also if you have an identity column for dealing with T SQL here, it does reset the identity value back to where it starts. So that can be very beneficial. The Delete clause does not do that. So if you happen to use the Delete clause to remove all rows from a table does not reset the identity value back to the starting point. So that is one benefit of the truncate command. If that is what you’re going for. Now, you do have to be careful using truncate if there are foreign keys being used. So if there are any foreign keys that are referencing this particular table, truncate will not work. So that is also a limitation of the truncate command. But that prevents us from deleting on unintended rows from other tables that are referencing that. But journal syntax here that looks for for these commands, we have the a standard delete and then non standard delete and then our non standard truncate. So non standard here is going to allow us to use things like a an alias as part of our deletion. Whereas the standard delete does not. That’s the primary difference between the two. And then we also have a truncate command as well. And truncate is not a fancy standard it is specific to T SQL. Although you may see you may see similar types of operations in other database systems. But let’s take a look at some examples on how we might actually execute our SQL here. So for this video, there is a corresponding setup script that’s going to add all of our demo tables back into our database. So that person address, and so on, along with some starting values here.

So make sure you run that script before you start executing any of these queries here. And we can also take a look at what this actually looks like. So if we pull out all of that data as part of a just select star command, you can see all of our records here. So here is our person table and our person address table. So let’s first start off with a simple delete. So I’m going to start out by I want to delete, let’s say, Joe from our table. So delete from person address where Person ID equals two and address type id equals two. And then if we and this is not deleting the person itself, but deleting the person from address, so this would be person address ID here. So delete where the address type is two, and the person ID is two, just in case, if this person had more than one address being listed there, we can delete only one, that one specific one. So let’s give this a run. Alright, so you notice now, for Person ID two, I have deleted a one of their addresses, I have deleted their address that was of type two. And if we select star from demo, dot address, type. And execute that, you can see that I deleted that person’s work address, but not their home address. So I left their home address intact, but removed their work address. Now, of course, you can delete more than one row at a time. So we can say Delete. And remember the from here is optional, we don’t have to add, we don’t have to say delete from here, we can just say delete, and then the name of the table. So delete demo dot person address where personality equals one. And so if we look here, we have these two rows here. So Person ID one person ID one, they have a home address and work address. And so if we run this statement here, ah, now we are two rows shorts now. So we can see that the both of those were deleted as a result. We can also if we, if we like we can use sub queries as part of our deletes. So here, I am selecting a, you’re using a sub query here as part of the where clause for my delete, so delete person address where a person ID is three. So that’s this row here, where and where address type ID is work. So this is something like as I don’t know, what the identify the identifiers are for the each of the individual address types. Using a sub query here and using the more human readable version can can make our query a little bit more readable. And so there we delete that person’s work address. We can also delete all the rows in the table as well. But let’s first I’m running out of rows here to delete.

So let’s go ahead and run that setup script again real quick. So we get all of our rows back. And then let’s just outright delete demo person address. And so if we run this, we can see it deleted all rows from our table. And so of course we can check to see if there’s anything left and of course there isn’t because we deleted all the rows because we had no WHERE clause and so it just deleted everything. And of course we can also So use a, a delete, let me go ahead and run the startup query again, and then do the similar thing. So let’s say delete demo person where not exists. And then we select everything from the person address table. And the person ID, in that person address does not equal or equals the person ID from the person table. So if we run this zero rows affected, basically, this is just a delete deleting people who had no address. And so since I have all the addresses, then I don’t delete any, any people because everyone has an address. But if I go in, and let’s go ahead and delete all of the people again, there we go. And then when I, if I run this, I actually deleted four rows from my person table, because those are the people who had no addresses. So if we do this, select star from demo dot person and every Korea so now we have no people left because we deleted all of the records in the address table. And then we also deleted all the people who had no addresses. We also deleted the people who had no addresses. So that is some examples of sub queries. And what happens when you delete everything. T SQL also supports the non standard form, allowing the full FROM clause.

So what I’ve shown so far, with things like delete from demo dot person to address that is standard in the from clause, the from word there is not required. But we can actually let’s go ahead and also run the setup script again real quick, we can now actually have the full FROM clause. And so that means we can do aliases, we can do derive tables, all sorts of things as part of this. So now inside of my where not exists, I actually can say P dot Person ID, from my outside table, right from my outside table. So this is a very, again, a little bit more expressive, a little bit more user friendly, as far as being able to use aliases and things like that in T SQL, but again, this syntax here is not required. So meaning that you know, if we have our full from syntax, we can also use joints, and the standard ancy standard delete clause, we can actually utilize joints. But in T SQL, we are able to do that because we can use the full FROM clause. So here, delete pa from demo dot person address as PA, enter join on address type, where Person ID equals three and T dot name equals work. So before, right, I had to use a sub query. So let me bring that statement back up here. So these two clauses here are identical. So this is what we had before. This is anti standard. This is anti standard. This down here is SQL Server specific. And some other database systems may allow you to do this, but it is not part of the the the ancy standard. But as you can see here, it’s a little bit cleaner to read when compared to using a sub query. So the join allows us to do a little bit more, a little bit more of an expressive delete clause versus the anti standard which requires a sub query is to do the exact same thing. So if we run that I may delete this back here, and let’s execute that. There we go. And so you can see that I deleted one row and then pull the rest of the room. So I deleted Person ID three Where their address was work. So and they only had a work address, so I deleted it from completely from the table. So that is going to be mostly just mostly our delete clause.

So we’ve covered both of these syntax here, the standard deletion, and the non standard deletion. Remember, the primary difference here between standard and non standard is that in the standard, we can’t use the full from clauses just from table. But and the non standard delete supports the full, the full FROM clause. So we can use table sources, we can do joins, all of those sorts of things can be included here in the from clause, and the standard is just from table and then in non standard we can do from and then anything that goes into your standard FROM clause, as if it was a SELECT clause. Then we also are going to talk about our truncate here, as well. So truncate, just like our so truncate, just like where we deleted all rows, we can very quickly say truncate and truncate is going to remove everything from the demo address table. And so what is also nice about this is that if we see if we look at the identity column, the current ID ID is going to be one, because we have completely refreshed our table. So I can show you what happens here. And a little bit more detail. If we reset our tables, and then execute this again, you can see before the truncate our identity column is six after the truncate is one. This is again faster than saying this is faster than saying just this delete here. And as well. I load this back up. And this, you can see that my identity column, when I do delete results in the same identity, right, the identity doesn’t get reset. So six would be the next value that would be inserted for that identity column as a result of inserting a new record, but truncate here resets it back to wherever, whatever it started to. And you’re not going to really see the execution time here is is you know, I have very, very little data to deal with. But you can see that the total execution time is not going to be really it’s not going to match. This one is technically faster at the moment, because there’s just not enough rows here. But truncate in general for larger sets of data are going to be are going to be faster statements to just completely remove everything inside of our inside of our table. But that will conclude all of our statements that we have listed here to remove data from our database

Update

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at data modification again, but in particular, how we might update data that exists already inside of our database, updating data is going to be achieved using the update statements. And again, this is an atomic operation, meaning that either all rows are updated, or none of them are updated. So it’s an all or nothing statement, it cannot be interrupted there. And but again, we’ll talk more about transactions anatomist at another video. Up update does allow some non standard SQL statements. So very similar to our delete, we can also have the full FROM clause supports inside of T SQL, but not as part of ancy standard. And so we can also include variable assignments as part of this as well. But this, again, is non standard. When compared to other database languages or data database implementations like Postgres, or MySQL, they will have some slightly different syntax. So the syntax that we’ll be covering in this video includes the standard updates, which is listed here up at the top, so update, and then no FROM clause, right, or no from that update, and then the table name, and then set and then the columns that we want to set and then the value that we want to set those columns to, then we can also include an optional WHERE clause as as well. So if we want to update a specific row or set of rows, we can specify that using our search condition as part of the where non standard allows the use of a From clause in addition to that, so that’s going to be the additional item here. And we can also do variable assignments as part of this as well. So update and then set up here, I can’t actually use in standard SQL I can’t use I can’t set variable names or variable values as part of it. But we can do this inside of the non standard update in T SQL. And the table source here down here for the non standard update.

Again, we have the full well, you have the full functionality of the from clause. And so we can, that means we can use table expressions. And we can also use joins. But let’s take a look at an example of a few updates. So before you if you’re following along, running these SQL commands, as you’re watching the video, please make sure you rerun the setup SQL scripts along with this. So we have the starting data in our original demo tables. So first thing that I am going to do is a basic standard update. So here, I’m just going to showcase what the person address looks like now, and then what it looks like after. So what I’m going to update is the person address table set. And then the column which is line two, two big bird’s nest where person I do ID where Person ID is two and address type is two. And so if we run that, you see Person ID to address type two, which is row four. And so I’m setting line to line two is no currently. And then if I see here’s scoot this up a little bit, if I go down here and look at this row here, you can see now, instead of being No, it is Big Bird’s Nest. So simple update, right simple update, we have the table that we’re up to the table that we’re updating, and then the values that we’re setting what the associated columns, and then the where clause specifies which rows we are actually updating. Of course, we can update more than one row at a time. So if I do something like this, so update person address set. And now I can also set multiple columns, right, so the columns are separated by commas. So line two is going to be no city is going to be the little apple and then I’m also updating the updated on column where a person ID is one. And so if we run that, so Person ID one, those are these two rows here. And so if you look here, the city is now the little apple and The updated en is also updated to the current timestamp.

So, again, very simple update syntax as far as that goes. But you do want to be careful just like what the Delete clause, right? You don’t want to accidentally you know, for one, you don’t want to accidentally delete things that you don’t want to delete. And conversely, we also don’t want to delete or sorry, we also don’t want to update rows that we don’t want to update, because that removes data from our database, because we’re replacing it with something else. And so we want to be careful when we execute these commands, we can. History tables are a technique that we can use to undo updates and deletes. But traditionally, there is no way to there is no undo function. Standard, right. So if you delete something, it’s gone. If it’s updated, the data that was there is now replaced permanently with the new data. Now, like I said, Well, we’ll have a video later on in this course, that talks about how to implement history tables that can help undo some of those actions. But let’s look at some more examples of our updates. We can also update using sub queries. So just like what we have, with our delete clause, we can use sub queries in Update clause as well. That includes sub queries as part of the set, and where, so here, I am setting the address type to whatever the ID is of home, and the updated on and then we’re setting where the rows that we’re actually updating are going to be person three, where it’s work. So we’re actually we actually changed person ID three. And the address is work, we flipped those are, we’re going to flip that one, this one here, we’re going to flip this to be the home address. So if we run this SQL, now that is ID one, which happens to be the type of home. So sub queries are a powerful way to get some more flexibility out of our standard update sequence or our standard update. If we exclude by the way, if we exclude a where clause, it updates all rows. So it said Be very, very careful when executing updates or deletes because you can accidentally either update all rows or delete all rows, if you don’t write your where clause very carefully. So it is good practice to try out your queries on a demo or test database before you roll that out and do it on real data. Because of course, you can accidentally destroy information and not process and testing out your queries.

So just keep that in mind when you are running these updates. So I do want to showcase some non standard updates. So everything that I’ve showcased so far is anti standard. But now let’s do something that is a little bit more specific to SQL Server, I’m going to go ahead and run our startup script to get us back to what our original data was. And here I’m going to update all of our people are, sir are the records were? The first name is Fred Rogers. And the type is work. So this was identical, if you remember to, so this is person three here, person three is Fred Rogers. So that’s this this person, yes, the third person that was inserted, and the query that we had was this here. So if we split this and showcase these two, so the query on the right is identical to the query here on the left with the query on the left is not standard SQL. This is because I’m using a From clause and using a join. So the full from the from clause is not supported in the standard update that is supported in T SQL. So I can do joins, which allow me to not have to use sub queries as part of it as part of my update. So both of these queries here do the exact same Same thing, they both update the exact same record. But this one over here is a little bit more compact. Because I can take out one of the sub queries, which is this one down here inside of my WHERE clause because the full FROM clause is supported, I still have to use a sub sub query as part of the address type ID because I have to pull whatever address type the ID of whatever home is. But nonetheless, it does simplify our query a little bit by introducing the ability to do joins, I can also use variables inside of the nonstandard update. So in particular variable assignment. And so here is a variable I have declared up here, and I am setting it as sorry. About, I’m setting it as part inside of my set clause. So at updated on equals updated on, which is, this is the column name, this is the variable. So don’t confuse the variable with the column. And this is the current time. So what I’m doing here is I’m getting the current timestamp. Setting the column equal to that, and I’m also storing that value in the variable here. That is very, very nice if I want to recall that value as a result of the updates. So I can actually pull that value back out and and see what was actually set as part of the query result, instead of having to run a full select on the database, or on the on the original table itself. So just an example of a useful feature of the non standard updates in T SQL, being able to set do variable assignments as part of the set to clause in the updates. But that will conclude most of our work that we are going to focus on utilizing the update clause. Remember, just as a short recap, we covered the the standard syntax for updating, so update table and then set and then the list of columns and the values associated with those. And then we also covered the non standard update, where the non standard update allows the ability to use a full FROM clause so that includes things like joins and table expressions as well.

Merge

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at the MERGE statement as part of our data modification video series. So what data modification, remember, we’ve covered so far inserts, updates, and delete. But now we’re going to look at a way to combine those operations, The MERGE statement is going to allow you to both insert and update in a single statement. So the syntax is a little bit more complicated here. So we as follows We have merge and then the table that we’re targeting, and then using the source table, so let’s take a look at the syntax for merge. So the syntax here is a little bit more complicated than some of the other statements that we’ve seen so far. But we’ll go through some examples and see how it looks. But merge on the target table. So we’re merging two, using this table on this predicate. And so when this predicate finds a match, right when matched, do this, when not matched, do this. So we can do an update or delete here when matched or not matched, and vice versa, when matched, and when not matched are optional statements. So these are optional, but we’ll show some examples of how this works. And General. The statement here can include predicates for evaluating matches. This is mostly for verifying if changes actually happened as a result of some operation. And note that I’m actually saying matched here. For my predicate, we have this on keyword which is very similar to what we did with joins, right, and inside of a join that was defining the filter. But here, we’re not actually filtering any rows out, it’s determining whether or not a row matches or does not match the predicate. But it’s not necessarily filtering rows. So the operation is slightly different. But let’s take a look at an example of what merge looks like. So again, also, please make sure before, if you’re following along here, please make sure you run the setup query for the this video. And then let’s take a look at our first example. So I’m actually going to use a CTE here, to showcase this. So with CTE, this is just pulling out the information about Marie Jones.

And so selecting that data from the table there, and then I have this MERGE statement down here. So merge person address. So this is my target table that I’m merging to using my CTE as the source of information source source CTE on s dot Person ID equals pa dot Person ID and address ID matches. So basically, I am my match condition is going to be the person ID from my CTE matches the person ID in the target table. And the the address ID or address type ID from the source, CTE matches the address type ID in the person address table. So again, these are matches not filter, so we’re not filtering rows, we’re matching rows. And so when a row matches, we do this statement. So we’re updating line one to line one, so on so forth, and updating the updated on. So if there is a match, I’m just updating the record in my table to be the record from the CTE the matching row in the CTE. When not matched, we’re going to insert right. So when there isn’t a match, we can actually pull from the target table. Right, but if, or and the source here, right, so we have no targets to actually compare to. So when we’re when we have a match, we can take the source, right, the source we go back here to the syntax here. When matched, we can update or delete the target table Using the source table, when we don’t have a match, then we can insert data into the target table from the source table. So here we are, when the records don’t match, so when the row from the source CTE source CTAs doesn’t match the record and the person address table, then we’re going to just straight out insert it. So that record essentially doesn’t exist. And so therefore, I’m going to insert it instead of updating it. So let’s go ahead and execute this. And that, and there we go. And so I’ll take myself off the screen here for a second. So we see, I am looking for Marie Jones, looking for Marie Jones. And we can actually see the record that we’ve updated. That’s the last two rows here, Marie Jones and Marie Jones down here. And we we have situations. Okay. So Marie Jones, originally, if we look back down here to our original table, right, only had one row, right, our original address table had only one row for Marie Jones. And she had a work address, but not a home address. And so up here, I gave my CTE both a work and a home address for the work address, I just updated it.

For the work address, I just updated it to be to be what was in the matching row here that now matches this row. So because the name and the address type matched, and so this is now PO Box 123, at Sunny Hill, which changed, right this was this is snowy drive. And so I changed her work address. And then I inserted a new record for Marie for her home address. So remember, right remember, the the merge predicate on Person ID equals Person ID and address type equals address type. This is not a filter, this is not a filter, it is a matching clause. So if and that only determines right, this match this predicate here determines on if we if we run the matched or not matched. And again, these are optional. So I can I don’t have to have a not match action. And I don’t have to have a matched action, I can have one or the other or both. I can have one or the other, or both. So all of these other rows, these, this first five, these first five rows in my table, were completely ignored for four matches because they did not match the person ID for Marie Jones, right, because all of these people appear were not Marie Jones, so therefore they did not get updated or records were not inserted on their behalf. So let’s use us check out a another example here. So similar CTE, just a different, I’m changing her work address back or home address back and work address back. Let’s go ahead and execute this. And I have when matched and the and the values in the column, write differ, then update, write else but not match just insert. So the not match part is still the same. But the new part here is that I have a predicate for my matched. Now I have a predicate for my matched. So again, the original predicate and the USING clause is do the record does the record from the source match what is in the target? If it does, then I’m going to check the this predicate. And if this predicate is true, then I’m going to do the update only if this predicate is true. So let’s go ahead and execute this. Oh, zero rows affected zero rows affected. So we have an error somewhere. All right.

So really, we have a the primary issue is we have nullable columns. We have nullable columns. And so With that, and if we go back here to look at our address table, person address change person address. So, line one is not null, line two is no. So that’s knowable. Really, really the difference is here, I can actually showcase you this, this statement here. This code is exactly the same as I just showed. But the difference is that instead of having no here, I’m actually providing a suite a for my line to have my address. And if we run this, again, though, we get zero rows affected zero rows affected, because we still don’t have a match, right? We still don’t have oh, well, we have a match. But our predicate is invalid, our predicate is invalid, primarily because we have this issue. Here, we have this issue here. Right? If we look at our duty to do so let me just do a select, sorry, showcases select and star from demo dot person address. And then if we go down here to Marie, line two, in both cases are not line two in both cases are null. And so even if we provide, even if we provide something right, remember comparisons to know Boolean comparisons to know do not work in SQL, because it becomes a three value predicate logic, meaning that we have true false and unknown. But when we are comparing something against No, it’s going to be unknown, because we don’t know what what the result is because the value is absent. So a correction to this. A correction to this is to use something like this. Correct. A correction is something like this. So when we have knowable columns, and we’re comparing something and getting small, we need to use the is operator, right? Is no or is not No, right is no or is not no. And so here, if our line two is no and line two is not and T dots are CTE and R. So if our CTE line is null and our source, our target table is not null. Basically, what I’m doing here is, this is a fancy way of just me doing that, are they different, right? Are the two different if the line if the line two is different, and my target and my source, then I’m going to update so if we run this now. Uh huh, there we go, we get one row affected, which should now show this record down here being line two to being not null, there we go. So they’re sweet A. So when we have this record here, so here’s my sweet A.

So when we have the same person, so when the person ID and the address type matches, so when the person is Marie Jones, and we are looking at her worker address, and the values differ from the source and the target, then we’re going to update it to match what our source table is, or our source information. So let’s take a look at the same example here, but a little bit cleaner, because I can actually use our, our set operators to improve on that really long winded comparison of comparing whether or not the source and the target are the same or not. And so a really interesting way to do this is using the intersect set operator. So if the source and the target intersect, then we don’t update because they’re both the same. But if the intersection does not exist, meaning that they are different, then we update and so that is a really useful, useful way to do that. And if we run that, we can actually see the end result of this. Getting up Data is here. But that is our merge right? Our merge is going to allow us to combine, update or deletes along with an insert. So when matched, we can either update or delete records. So the match between the source table matching the rec the row in the source table matching the row and the target table on this predicate, and then we can also perform an insertion if a match does not exist on the target.

Output

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at how we might see the results of any data modifications that happen as part of our SQL queries. So, in order to see more fine grained details of what happens when we update, delete, or inserts will use the output command, or the output clause is going to return information about anything that was affected as a result of our queries. This is supported actually on all DML statements. So this includes the SELECT clause as well. But it’s most commonly used when we insert, delete or update records. This can also help provide information along with a merge or merge clause or a MERGE statement as well, particularly with the action function, which this allows you to see which operation was performed on each row. So before when we ran our merge statements, we could only see the number of rows affected, and not which ones were updated and which ones were inserted or deleted and things like that. So we can actually check to see what was done on a row by row basis with the merge. And it can also help transfer output into another table. All But although the output table itself can’t have any relationships associated with it in this case, but this in particular, is very useful for creating history tables. And so if you accidentally, you know, if you’re running like a delete, or update, you can actually capture that information before it’s deleted or updated. And so you can restore that information after that action has actually happened if you need to. But let’s take a look at an example of what output looks like.

So what I’m going to show here, and be sure to rerun the startup script, so we get our original table or our original values back into our table. But I’m going to use the same merge clause that I originally started with in the previous video. And so merge person address with my CTE on personality and address type matches. And if it matches, if the IDS match, then updates the target table with the source table. And when it doesn’t match, just insert it. But the new thing as part of this is the output. And in particular, I’m highlighting the ability to do action. And we can actually add output to any, like I said any DML statements, we can add the output command. But it’s like said most useful for things like merge and insert updates. But for our output command, and I’m going to do the action as operation. And then what I’m actually showing here is, whatever was inserted, show it, whatever was deleted, show it. Now also keep in mind, an update counts as a delete, right, and update counts as a delete. An update is a deletion that only deletes parts of a row, but not all of it. Right. So that’s really the that’s really the the syntax here. So if we run this statement, yeah, we can see the two rows that we actually, that actually happened. So we inserted this new row here, as Remember, our person up here, remember that Marie had only a work address in our existing address table and not a home address. So we inserted the home address. So that was an indication that it is that the there was not a match, right. And then for this one, there was a match because we hit our update clause in our merge. And so this is the operation that actually happened. And then we can see, if we scroll to the right here, we can look at the inserted right as part of an update, these are the values that were that replaced the existing values. And then the deleted was the values that were there before. And so this was on the far right hand side, these are all the values that were there before the merge happened. And then these are the values that were actually updated.

So these are the values that were set. So you can see that this outputs. This output feature with particularly with the action is very useful, because now instead of just seeing the rows affected, we get the data itself back out on on the roses. Okay, so we can see what was actually inserted, not just the number of rows that were inserted. And we can also see what rows were updated. And with updates, we can see what was there and what is there now, which is very useful. And then, in particular, for keeping track of things like history and things like that. And then and in terms of a pure deletion, in terms of a pure deletion, we would have delete here as the operation and nothing for the inserted and just the deleted values. That would be the primary difference there. What is the practical but what is the practical benefit here? Well, the practical benefit, as I mentioned, here is history. Right? If we update something, or delete something, we want to keep track of it. Because a lot of times, that helps us run reports as part of our application or our our database. Reports are very expressive way to kind of track data and see what’s going on what’s happening with our users. And likewise, if an action was completed, unintentionally, the tracking of that information of what happens, updates and deletes and things like that allows us to undo those sorts of operations. So this, this query here is identical to the one that I had before. But now, I am going to use this output action as into, and this person address table. So let me actually run my setup again. And here you can see that address change table, this is to keep track of all the times that a person changes their address. And so if we actually execute this now, we can see that we have two changes to our address. Right, this is our Insert and our updates. And so we had a change here we had an insert for person a person for So Marie Marie inserted a new a work address, and then updated her home address. And of course, you know, we could we could include all of the extra information here that you know, the what was actually inserted, we can include that here, and what was updated what what what it was and what it is now, we could also include that here. I’m not going to include that, that much detail in this particular video.

We’ll pick this information back up in a later video, where we talk about the strategies behind different types of history tracking that we can implement as part of our database. But for now, the output command is something that is very useful in keeping track of changes that happen, particularly with updates, inserts and deletes although it can also be used as part of the SELECT clause which is also a DML statements. But that will conclude our videos on data modification