Temp Tables

What's going on, everybody? Welcome back to another SQL 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. Um it's just a little bit different, and you can hit off of this temp table 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 uh throughout this video. But let's get started right away with actually creating one, looking at it, inserting some data, and and and kind of showing you how temp tables work and what we can do with them. So uh we're going to start off with CREATE TABLE, much like uh a regular table is created. The only difference is we're going to do this pound sign and then we're going to do temp_ employee. Uh 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 uh let's just start by doing EmployeeID, we'll make that an integer, we'll do JobTitle and we'll make that a VARCHAR(100), and then we'll do Salary, and let's make that an integer. And so now we have our temp table. Uh 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 um 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 cuz I'm going to get to a little bit more interesting stuff in a second. Oops. So we'll make this person HR uh as their job title and then for salary we'll give them 45,000 and close that 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 we don't have to create this every single time we um or we don't have to run this every single time we need to hit off of it like we did a CTE, if you watched my previous video. In this one, we can just run it, and it sits there, and so uh again, it feels very much like a real table. And I'm going to get to a little bit of the nuances of of the in the differences between a regular table and a temp table in a second. But let's really quickly, um we want more data in there. You don't have to just um do it value by value, we can also just do um uh where we select all of the data from a specific table and insert that into a temp table. And that is really quickly you know, how I do it most of the time. Most of the time, I'm not inserting values. Um I am you know, 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 EmployeeSalary and then we just stuck it into this table. And really quickly, this is one of the big uses of a temp table. We had Let Let's say, for example, that this EmployeeSalary table had a billion rows or or or just an extremely large number and we were trying to uh you know, hit a somewhat complex query off of it, where we're using joins and we're using uh maybe some window functions or different things, you know, 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 uh that sub-section of data that we're wanting to use for all of our later queries. So really quickly, that's kind of um 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. Uh let's do temp—oops. CREATE TABLE. Uh let's do temp _Employee2. And then let's do open parenthesis and we'll do JobTitle and we'll make that a VARCHAR (50). And then we can do Employees PerJob, we'll make that an integer. Now we need AvgAge, make that an integer, and the very last one will be Avg 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_Employee2. 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 uh 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, we're getting a count on the job title, average age, average salary, and that is it. Um 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 wanted 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 uh uh processing power. And it will reduce your runtime dramatically when you're placing this data in this temp table and hitting off of that instead of all these joins and everything above. Uh a lot of times these temp tables are used in stored procedures. Now, if you haven't learned about stored procedures or used stored procedures at all, you know, that's okay. I still want to show you something that might be useful um although this is used a ton in stored procedures. So for example, let's say we have a stored procedure set up. We run the stored procedure and we get an output, and you know, we for whatever reason want to run it again, and when we run it again uh we get this error. And you know, this temp table lives somewhere. It It It doesn't live in an actual in the actual database, uh but it lives somewhere and so when we run it again, we get an error because there's already a temp table created. One trick or one little tip that I would give is doing something like this, saying DROP TABLE—oops, I don't know why I did so many spaces. DROP TABLE IF EXISTS, and we'll do temp_Employee2, just like that. Now, what this is going to do is when you're running that stored procedure over and over and over again, you're getting a 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 uh 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 of the technical aspects or the DBA stuff. Um 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 uh 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.