SQL Server Programming Part 8 - Temporary Tables

welcome to this YSL tutorial in this video we're going to teach you how to use temporary tables in Microsoft sequel server we'll begin this session with a quick explanation of what temporary tables are we'll then move on and show you two ways that you can use to create a temporary table and then how you can add records to a table that you've just created once we've covered the basics we'll move on and show you a few of the more technical details of how temporary tables work we'll show you where you can find a temporary table that you've created we'll explain the scope of a temporary table and how you can extend it by using something called global temporary tables we'll show you a couple of methods for getting temporary tables to be removed from a database and finally we'll explain how temporary tables work when you're using store procedures as well so quite a lot to get through let's get started a temporary table is exactly what its name suggests it's a table which only exists temporarily within a database in sequel server you'll most commonly use them to hold the intermediate stages for long sequence of operations you can kind of think of it like the process you go through if you are buying a new car see it starts as we have here on the left-hand side with a list of all of the possible cars models you could buy you might then narrow that down into a short list of the most interesting models and then perform a slightly more detailed comparison between them from that short list you then extract the final results whatever that may be and that is our analogy for how ten tables work so in sequel server terms you might start with a full table of data here is a full table of films from there you'd extract a subset of those records into a temp table or a temporary table and from there you could come up with some sort of final result listed this video is going to concentrate on the intermediate stage there how you build a temporary table there are two techniques for creating a temporary table in sequel server the first one I'm going to show you simply involves using an existing select statement so I've written a query here which returns two columns and a number of Records for films containing the word star in their title instead of selecting those records what I'd like to do is get them insert into a brand new table and I can do that by adding the into clause to my slide statement and after the into keyword I use the name of my temporary table so all temp table names begin with a hash symbol following that you need to think of a sensible descriptive name mine's gonna be quite boring actually it's gonna be called temp films without the capital T actually there we go so what would happen if I executed this query now is it rather than seeing the results all the records that match my criteria will be inserted into a temporary table so if I do actually want to see the results I'll need to subsequently select those from my hash temp films table and there we go so if I execute this query I'm actually performing two operations here first of all I'm still I'm inserting all the right matching records from the film table into a new table then subsequently selecting them from it in fact if I look at the messages tab I should see clearly two separate things have happened this one is the records being inserted into the temp table this one is the records being selected from it the second method for creating a temporary table involves bit more effort but gives you a bit more power and control over what you actually create so we have the same select statements as previously returning films whose name contains a word star might like those records to be inserted into a temporary table to do that I'm going to write an explicit create table statement which will create a table called hash temp films same boring name as before now this table I'm going to define the columns that is going to contain so in a set of parentheses I am going to declare two fields or two columns one called title whose data type will be varchar' Max and another column separated with a comma called release date and that data type will be date/time all I need to do now once the table has been created is insert the results of this select statement into it and I can do that when an insert into statement insert into hash temp films the results of my select statement again if I want to see any sort of results or just to at least prove that the the records have been inserted into them I need to select everything from hash temp films at the end and if I execute this query I should see exactly the same results before with my list of 17 films whose name contains a word star a quick look at the messages tab just game proves that two separate operations of effective records here the first one which inserts records into the temporary table and the second one which selects the records from it once you've executed some code which creates a temp table you actually find it within the object Explorer but not within the database you're working in so in the movies database here you won't find the temp table temp films it's actually stored as part of the system databases folder expand that folder there's a database in there called temp DB and within there there's a folder specifically for temporary tables we're going got one in there so far and it's the one that we've just created in the script hash temp films and one thing you'll notice when you look at the temporary tables folder is that the temp tables you create as well as having their short name here hash temp films they've also got a very long extension which uniquely identifies them the reason for this is that the temp DB database is a shared resource so lots of different connections can create temporary tables and more than one might create a temporary table called hash temp films now that the temp table is only visible to the connection which creates it I'll demonstrate that clearly in a moment what I can do to prove this is if I copy the code which creates a temp table add it to a new query and execute it when i refresh my temp tables folder I'll find two instances of hash temp films but each one will have its own unique identifier sitting right at the end now each instance of the hash temp films table can only be referenced by the connection which created it so if I went back to my original page where I created the table and then inserted some records into it I can highlight this command the Select statement and execute that independently to return all the records stored in that instance of the temp table so that's referring back to the original number seven as it is in this case if I copy that same select statement and place that into the new connection we've created now we've created a table but not inserted any records if I select and execute that query I'll find that I don't get any records returned at all finally if I copy this select segment again and place it into a completely brand-new query in a new connection which does not have an instance of temp tables sort of the temp table hashed em films if I execute this query after an error because the hash temp films table doesn't exist to this connection now it is possible to create a temporary table which can be referenced by multiple different connections I'm going to copy the entire script here which create an inserts records into a temp films table I'm going to create a brand new connection paste it in I'm simply going to change the name to have double hash marks in front of the table name so double hash indicates what refer to as a global temporary table whereas the previous ones are the single hash are local temporary tables so if I execute this query now to create and insert some records and then select them as well I'll see that I get exactly the same results as earlier and if i refresh my temporary tables folder I'll find that I get a new hash hash temp films table the big difference here is that the global temporary table doesn't have a unique identifier that's because it can be referenced from any single connection so there's just one instance of that global temple so if I copy this select statement which selects records from hash hash temp films and create yet another new query if I paste in the Select statement and then execute it I will return a set of results from the global tempting it's kind of difficult to think of a reason why you want to do that if you wanted lots of connections to be able to reference the same table it will probably make more sense to create a permanent table in the database but that's what they are and that's how they work should you ever find yourself in a situation where you need to use one temp tables delete themselves when the connection which was used to create becomes closed so if I head back to my original query which was used to create our first hash table here I'm going to close the connection down without saving the changes if i refresh the temporary tables folder at this point what I should see is that one of my two local temp tables is removed automatically it's also possible to explicitly delete a temp table which is particularly useful if you've got a very long sequence of code to run and you want to get rid of the temp table as soon as you finish with it so I can do that by heading to the script which created our second local temp table and I'm going to write the same as drop table hash temp films now again if I select and execute that single statement and then refresh my temporary tables folder I'll find that my other local temp table has been removed as well global template balls are likewise removed when the connection which created them is closed so if I close down this connection don't bother saving the changes refresh the temp tables folder I'll find that my global table is removed as well the one exception to that is that if there were any other transact SQL statements being executed on that temp table at the time then the table is only deleted when all those transactions have been completed other than that um that's how temp tables are created and removed earlier we talked about the scope of temporary tables and in particular how you could only reference a local temporary table from the connection in which it was created now one slight variation on that is when you're using temporary tables alongside store procedures in this example we have a command to create a basic temporary table containing the same two columns as earlier and then we have two calls to separate stored procedures one which tries to insert records into the temp table we just created passing in a keyword here which dictates which records are affected and then the second stored procedure which will select records from that temp table now we've produced videos on how to create a new store procedures previously but I'd like to quickly show you the code used to create these two let's look at the one which inserts records into our temp table first the setup of the procedure is fairly straightforward it's accepting the single text parameter which affects which results are inserted the key thing is that the hash temp films table is referenced even though it doesn't exist in this context i've already executed the code to create this procedure let's look at the second one as well this is even more simple it simply selecting records from our temp table again the intellisense flags up the fact that our hash temp films table does not exist in this context but if I execute the code it works perfectly commands completed successfully now that I've created the two stored procedures I can head back to the original page and execute the entire thing in one go and what I'd expect to see is an entire list of results just as though all these operations have been performed in the same connection because technically they have all of the code that was used to create the store procedures have no reference to hash temp films when the still procedures are executed they can reference it as long as it's in the same connection so just to clarify that if I were to take these two commands and place them into a brand new connection and try to exclude them here I'll find that they both fail because neither of them has a reference to hash temp films hopefully this video has given you a basic understanding of how to create use and remove temporary tables in a database for the next video in this series we're going to move on and show you how to use something called table variables now these perform a very similar role to temp tables but with a couple of minor advantages that you might find useful if you've enjoyed this training video you can find many more online training resources at ww-why Zelko UK

Channel: WiseOwlTutorials

Views: 75,249

Keywords: sql, temporary tables, #temp, temp, table, wise owl

Id: 3ZtYrELHP8M

Length: 13min 27sec (807 seconds)

Published: Wed Feb 06 2013