 What's going on everybody welcome back to another sequel tutorial today we're looking at temp tables and if you can guess it based off of the name they're kind of like temporary tables and we create them very much the same way we're going to do create table it's just a little bit different and you can hit off of this multiple times which you cannot do with something like a CTE or a subquery where you can only use it one time or with a subquery you need to write it multiple times within a query. And so these temp tables are extremely useful I'm going to kind of talk about how you can use them as we're going throughout this video. But let's get started right away with actually creating one looking at it inserting some data and kind of showing you how temp tables work and what we can do with them. So we're going to start off with create table much like a regular tables created the only difference is we're going to do this pound signed and then we're going to do temp underscore employee. So literally the only difference between a regular table and a temp table is this right here at the very beginning this this pound sign. So let's just start by doing employee ID make that an integer we'll do job title and we'll make that a bar char 100 and then we'll do salary and let's make that integer. And so now we have our temp table. Let's go ahead and create it. So now we have our temp table created and so we can look at it really quick. So let's select everything from and we'll do temp employee. So let's take a look. It's completely empty and we can insert data very much the same way we'd insert data into a regular table. So let's start doing that. Let's do insert into. And we'll do temp employee and we'll do values and let's just do something really quick. So I'm going to get to a little bit more interesting stuff in a second. Oops. We'll make this person HR third job title and then for salary we'll give them 45,000 and close it off. So let's run this and let's select everything again and see what's in there. Perfect. So we were able to insert data into this temp table and again we don't have to create this every single time we or we don't have to run this every single time we need to hit off of it like we did a CTE you watch my previous video. And this one we can just run it and it sits there. And so again it feels very much like a real table and I'm going to get to a little bit of the nuances of the differences between a regular table and a temp table in a second. But let's really quickly we want more data in there. You don't have to just do it value by value. We can also just do where we select all of the data from a specific table and insert that into a temp table. And that is really quickly how I do it most of the time. Most of the time I'm not inserting values. I am taking a large table and taking a subset of that and then sticking it into a temp table. So let's look at this really quick and run that. So now we took all of the data from employee salary and then we just stuck it into this table and really quickly this is one of the big uses of a temp table. Let's say for example that this employee salary table had a billion rows or just an extremely large number and we were trying to hit a somewhat complex query off of it. We're using joins and we're using maybe some window functions or different things. It would take a very long time to hit off of this. But what we can do is we could insert that data into this temp table and then we can hit off the temp table and it already has that sub section of data that we're wanting to use for all of our later queries. So really quickly that's kind of a use case for that. So let's go down here we're going to kind of create another one and this one's going to be a little bit more advanced and a little bit of how I would actually use a temp table above was just kind of showing the basic syntax how you kind of put data into it. You know kind of how it's used. Now I'm going to show you kind of how I would actually use it. So let's do create table. Let's do temp. Oops. Create table. Let's do temp employee to and then let's do open parentheses and we'll do job title and we'll make that a bar char 50. And then we can do employees per job make that an integer. Now we need average age make that an integer and the very last one will be average salary and make that an integer as well. And let's run this. Oops. So we have our second table. Now we want to insert data into this one. So we're just going to do insert into and we'll do temp employee to and for this one. I'm going to take a query that we used in a previous video and so I'm just going to copy and paste that to save time and then we'll keep on moving from there. All right. So I'm just going to paste that in. We will run this and really all it's doing is from this from these tables it's taking the job title reading account on the job title average age average salary and that is it. So let's see if that worked which it looks like it did but you know let's actually take a look at the data. And so now we have this subsection of data from this join above. And what this is going to do is whenever we want to run this we don't have to run it on these two tables and create the join and then do the calculations which takes time. What it's going to do is it's going to take this these exact values and place this into this temporary table and if we want to run further calculations on these values. We can easily do that in a fraction of the time instead of having to run this every single time which will take up so much processing power and it will reduce your runtime dramatically when you're placing this data in this timetable and hitting off of that instead of all these joins and everything above. A lot of times these timetables are used in store procedures now if you haven't learned about store procedures or used stored procedures at all. You know that's okay I still want to show you something that might be useful. Although this is used a ton in store procedures. So for example let's say we have a store procedure set up. We run the store procedure and we get an output and you know we forever reason want to run it again and when we run it again. We get this error and you know this timetable lives somewhere it doesn't live in the actual database but it lives somewhere and so when we run it again we get an error because there's already a timetable created. One trick or one little tip that I would give is doing something like this saying drop table I don't know why I did so many spaces drop table if exists. And we'll do temp employee to just like that. Now what this is going to do is when you're running that store procedure over and over and over again you're getting an error or whatever for whatever reason you need to run it multiple times. Every time that you run it it's going to encounter this and so if that already exists it is going to delete that table and then allow you to create it again. And this is just a really good thing to do. So now if you see down below I can run this time and time and time again. And it is going to work every single time because it is checking to see if that exists and if it does it deletes it and then I can create again. And so that is just a helpful tip if you're going to try to use this. I highly recommend adding that to your query just to make sure things run smoothly. I know there is a lot more that can go into temp tables a lot more the technical aspects of the DBA stuff. Obviously I just want to teach you how to use it and what you might use it for and how to actually write it out. But you know there are a lot more things that you can do research on about processing speed and storage. But unless you are something like a DBA you probably don't need to worry about those things. And so if you are a DBA I do recommend looking into those things making sure you understand how that works how this data is stored. So that when people use them or you are using them you know what's going on in the background. But for getting up and running with temp tables I hope that this was helpful. Thank you guys so much for watching. I really appreciate it. If you like this video be sure to like and subscribe below and I'll see you in the next video.