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