Detailed Notes
1. Temp tables
delimiter // create or replace procedure tutorial.temp_table() BEGIN CREATE TEMPORARY TABLE t_mystuff( id integer, name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL, state varchar(2), zip integer, type_name varchar(100) ); create index temp_idx on t_mystuff(id);
insert into t_mystuff select t_one_id,a.address_name,a.city,a.state,a.zip,t.name from address a inner join lkp_type t on t.type_id = a.type_id;
select * from t_mystuff;
END; // delimiter ;
select t_one_id,a.address_name,a.city,a.state,a.zip,t.name from address a inner join lkp_type t on t.type_id = a.type_id;
Transcript Text
[Music] well hello and welcome back we are continuing looking at our working our way through sql uh tutorials and examples and today we are going to continue looking at stored procedures but we're going to look at something that is not stored procedures particularly but just i think used rather often or at least i know i have and that is the idea of temporary tables now a temporary table as we can see here the command is very much like creating tables in generals as we you know the general kind of things we've done to this point and it's essentially supports all of the same things the difference is instead of create table you put this temporary and what that does is it says this table is only going to exist for this session for example in a sword procedure that you're going to go ahead run it throw it out there create it just use it while it's there and then you're done you don't have to worry about it anymore this is something that's basically for i saw like a helper kind of table so i can gather some data and that's where we usually use it in a stored procedure so that's what i'm gonna focus with today and i'm gonna use a simple example as always but i think this will give you something to you know sort of work on and look for as we move forward and see where this is just one of the tools we're going to have to do some of the complex data manipulation stuff that's out there so let's go back to let's start with i want to start with the query just to show and that is this query uh well let me go ahead and do this let me drop that table uh t my stuff and i'll show you working around with that table here in just a minute now let's take this query and what i'm going to do is pull data from that address table and i'm going to pull data from the lookup type and if you remember that lookup type gives you a address type a name for it and it has an id and having this query if i was going to run this query a bunch or if i was going to have to do it over maybe a large set of data or something like that there are there are definitely performance issues when you start putting a lot of table joins together and particularly now this one's very simple but let's say i had i don't know like you know 10 or 15 different look up type values sitting in my table and had to join all of those then doing so bringing all that in and then working with it may be something that's not that doesn't make sense performance wise it's just a little too slow instead what we can do is we basically we're effectively going to denormalize this data instead of having our id we're going to have the name so when we run this query we're going to see here that we have our we're selecting the id city state zip the address name so this would be like maybe a street address or something like that but now we also have this name which is the type so each of these were business addresses and this doesn't have that that lookup type id and so now this idea of the temp table is well what if we wanted to keep this data around for a little bit we don't necessarily this isn't um like a source table this is something where we it's more or less like almost like a reporting type table we want to take this and we're going to do something with it and so let's go ahead and get rid of the normalization and then just do stuff with this with this data already combined out or resolved out and what we want to do then is we're going to go in and we're going to create something called temp table and let's see if i can get all of that right see if i copy paste it correctly and so what we have here is we've created this new procedure called temp table and so you can see we don't have our our t underscore my stuff doesn't exist and what this procedure does is it's going to come in it creates a table it's going to insert data into that table and that data is the it's inserting into tmi stuff and what i'm doing here i've got that id name city state zip and type name and i'm just going to take that same query i just showed that gives me this data and i'm going to throw it into this and now here even like notice that i'm going to rename it i'm not going to call it the t1 id i'm just going to call it id and then because this is a simple stored procedure i'm just going to do select star from this temp table we could do this with a regular table but this is something where we and we're saying hey we don't want this thing to to sit around so if i do call where is that temp table then it comes through does that suck star and i see that this is in that query basically that i just showed you however you know now it's with the these are the field names in that temp table and again if this was for reporting or something like that that may be the best way for you to go about it is that you can actually take whatever the names are in the source database and essentially i guess hide them or relabel them in a temp table and then kick this output out now you can do that with aliases and stuff like that but just yet another little uh bit of work or you know bonus that you can add while you're doing this now with this let's go back into our show tables and we're gonna see that although i created tmi stuff it does not exist it's not there because it was only there in this case the scope was only during the um the execution of the stored procedure now if i go back and do where was that create let me just do it this way but now if i take the same thing and i do create temporary table my stuff if i do it inside just a normal session oh it says already exist so it's not even showing it in the um it is not showing it everywhere i wonder if because it's out there it didn't get cleaned up but it also doesn't show when i do show tables yeah so it doesn't even show up for here because it was owned by oh because it's owned by the um the store procedure if i do drop table t underscore my stuff then okay now he's gone but if i take the same thing and now i'm to create them i quit out let's see if he's still there because it sometimes changes stuff so if i do use it shoot tables uh let's see if i just select star from t underscore my stuff yeah it says it doesn't exist if i try to create it it's not there because i had it's a temporary table and so once i quit out of this session it's gone and so with that stored procedure typically your session is going to be opened and then closed and then you're done now you so it's not the stored procedure itself i misstated that it's the let me jump back in here it is the session you're in so if you connect and run a couple of store procedures and then disconnect the temp table will disappear but you could have multiple stored procedures working on that temp table but now in this case i'm not seeing it because i didn't i did not set up everything to see it this is going to show you only your regular tables but again it's one of those things you don't really have to keep track of it because once you close the session out then your data is gone but of course that's the challenge is you don't want to be in a situation where you do a lot of work building out a temp table where you really want to keep that where you want to keep that information and then as soon as you lose a session then you know it's basically it's all all those queries and everything have to be rerun but with the temp table you can even um i'm pretty sure let's go do this um so let's do create index temp idx on t my stuff uh let's just do name let's do the id so now let's go back and see how that works for us and so when we do it everything's there and if we go back and do the call how far back is that okay that's back a little bit there it is oh i needed to use tutor sorry tutorial tutorial there we go and so it runs through and so this time it actually created an index in there so we which again show you can create multiple tables you can do foreign keys you can create indexes there's a lot that you can do and in particular again when you're in a stored procedure these are very useful because you're probably depending on what you're doing you may have some subset of data that you're trying to build out and you're wanting to work with that you're wanting to manipulate that and it may not be the same use that that data is in the source database particularly if you're like a let's say you've got a transactional kind of database where you're storing i don't know very transactional let's say that you have a database that's storing credit card transactions for sales for your company and you've got all this data in here and all it's doing typically is inserting new transaction records and so you don't want to have a lot of indexes on it you don't want to have a lot of overhead because you want that insert to just go quickly and be done you don't want to have to worry about stuff reindexing and slowing down particularly as the t table gets large but if you want to do a bunch of queries on that then of course one thing you can do is just go create a whole other database pull that stuff out put your indexes on things like that but you can also do it i will say sort of on the fly by creating a temp table and create that table pull the data over set up your indexes and then go work on the data that way so work on it from that temp table as opposed to your actual table and you can there are some other things we're going to see that are similar approaches to doing this kind of stuff but a temp table is one of those that it's just such an easy way to do that sort of that sort of work on a regular basis particularly within the database if you're going from a programming language what you would probably do is you would do a query you pull the result set back into your your language into your application and then on that you know on that application side you're going to walk through it you're going to do your manipulations or calculations or whatever it is that you need to do well what this gives you is an easy way to build out you know a result set that you can work with and now we're going to work look at some other things we can do as we get further into our stored procedures but this one i really just want to make sure that i covered temp tables and talked about those for a bit because i do think they're going to be you know useful for us moving forward and it's just one of those that i figured also would be a pretty good you know like one day topic kind of thing so you know bottom line don't be afraid to play around with commands we've already learned you know we've already looked at and even some of the others within your stored procedures you can do a lot of stuff now like i said i could create a whole table here there's it becomes complicated if you're going to start doing dynamic sql and things like that but yeah i could have stored procedures that take parameters and based on those they go out and build tables and they go out and do data loads and all kinds of stuff particularly if you wanted to somehow have a way to generate multiple in my sequel databases that are you know like make one per customer stuff like that if you want to do some sort of vertical you know siloing and stuff like that so you have one database that you hit and that's where you get your data you don't have to worry about data crossing over from one you know that's one group can access that can also be accessed by somebody else as well but we get into that's getting into a lot of i guess some of the more advanced kinds of things that we're going to talk about later most likely if not in this session or this series than in some of the others because that does get a little bit more into security over just being in a database that being said i digress a bit so i just wanted to point this out temporary tables they just remember they last for the session so if you do some stuff and you're like on that command line if you're sitting here on a command line and you create a temporary table and you quit and come back it ain't going to be there and you're gonna have to rebuild it which again goes back to the whole idea of you know let's store this stuff out start a file somewhere and execute that file or you know maybe it's a stored procedure so you can export execute that uh all within that stored procedure and then all is good that being said it's time to wrap it up for today keep this i guess shortish and give you a little bit of time back as always go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing looking at our working our
way through sql
uh tutorials and examples and today
we are going to continue looking at
stored procedures but we're going to
look at something that is
not stored procedures particularly but
just
i think used rather often or at least i
know i have
and that is the idea of temporary tables
now a temporary table as we can see here
the command is very much like creating
tables in generals as we you know the
general kind of things we've done to
this point and it's essentially supports
all of the same things
the difference
is instead of create table you put this
temporary
and what that does is it says this table
is only going to exist for this session
for example
in a sword procedure that you're going
to go ahead run it throw it out there
create it just use it while it's there
and then you're done you don't have to
worry about it anymore this is something
that's
basically for
i saw like a helper kind of table so i
can gather some data and that's
where we usually use it in a stored
procedure so that's what i'm gonna focus
with today
and i'm gonna use a simple example as
always but
i think this will give you something to
you know sort of
work on and look for
as we move forward and see where this is
just one of the tools we're going to
have to do some of the complex data
manipulation stuff that's out there
so
let's go back to
let's start with i want to start with
the query just to show
and that is
this query
uh well let me go ahead and do this let
me drop that table
uh t my stuff and i'll show you working
around with that table here in just a
minute
now let's take this query
and what i'm going to do
is
pull data from that address table and
i'm going to pull data from the lookup
type
and if you remember that lookup type
gives you a address type
a name for it and it has an id and
having this query if i was going to run
this query a bunch
or if i was going to have to do it over
maybe a large set of data or something
like that there are
there are definitely performance
issues when you start putting a lot of
table joins together
and particularly now this one's very
simple but let's say i had
i don't know like you know 10 or 15
different look up type values sitting in
my table
and had to join all of those
then
doing so bringing all that in and then
working with it may be something that's
not
that doesn't make sense performance wise
it's just a little too slow
instead what we can do is we basically
we're effectively going to denormalize
this data
instead of having our id we're going to
have the name so when we run this query
we're going to see here
that we have our we're selecting the id
city state zip the address name so this
would be like maybe a street address or
something like that but now we also have
this name which is the type so each of
these were business addresses
and this
doesn't have that that lookup type id
and so now this idea of the temp table
is well what if we wanted to keep this
data around for a little bit
we don't necessarily this isn't um
like a source table this is something
where we it's more or less like almost
like a reporting type table we want to
take this and we're going to do
something with it and so let's go ahead
and get rid of the normalization and
then just do stuff with this
with this data already
combined out or resolved out
and what we want to do then
is we're going to go in and we're going
to create
something called temp table
and
let's see if i can get all of that right
see if i copy paste it correctly
and so what we have here
is we've created this new procedure
called temp table
and
so you can see we don't have our
our t underscore my stuff doesn't exist
and what this procedure does is it's
going to come in it creates a table
it's going to insert data into that
table and that data is the it's
inserting into tmi stuff and what i'm
doing
here i've got that id name city state
zip and type name
and i'm just going to
take that same query i just showed that
gives me this data
and i'm going to throw it into this and
now here even like notice that i'm going
to rename it i'm not going to call it
the t1 id i'm just going to call it id
and then because this is a simple stored
procedure
i'm just going to do select star from
this temp table
we could do this with a regular table
but this is something where we and we're
saying hey we don't want this thing to
to sit around
so if i do call
where is that temp table
then it comes through
does that suck star and i see that this
is in that query basically that i just
showed you however you know now it's
with the these are the field names in
that temp table
and again if this was for reporting or
something like that that may be
the best way for you to go about it is
that you can actually take whatever the
names are
in the source database and essentially i
guess hide them or relabel them in a
temp table and then kick this output out
now you can do that with aliases and
stuff like that but
just yet another little
uh
bit of work or you know bonus that you
can add while you're doing this
now with this let's go back into our
show tables
and we're gonna see
that although
i created tmi stuff
it does not exist it's not there because
it was only there in this case the scope
was only during
the um the
execution of the stored procedure
now if i go back and do where was that
create
let me just do it this way
but now if i take the same thing and i
do create temporary table my stuff
if i do it inside just a normal
session
oh it says already exist so it's not
even showing it in the
um
it is not showing it everywhere i wonder
if
because it's out there it didn't get
cleaned up
but it also doesn't show
when i do show tables
yeah so it doesn't even show up for here
because it was owned by oh because it's
owned by the
um the store procedure
if i do drop
table
t underscore my stuff
then okay now he's gone but if i take
the same thing and now i'm to create
them
i quit out let's see if he's still there
because it sometimes changes stuff so if
i do use it
shoot
tables uh let's see if i just select
star from
t underscore my stuff
yeah it says it doesn't exist
if i try to create it
it's not there because i had it's a
temporary table and so once i quit out
of this session it's gone
and so with that stored procedure
typically your session is going to be
opened and then closed and then you're
done now you
so it's not the stored procedure itself
i misstated that it's the let me jump
back in here
it is the session you're in so if you
connect
and run a couple of store procedures and
then disconnect the temp table will
disappear but you could have multiple
stored procedures working on that temp
table
but now in this case i'm not seeing it
because i didn't i did not set up
everything to see it
this is going to show you only your
regular tables
but again it's one of those things you
don't really have to keep track of it
because once you close the session out
then your data is gone but of course
that's the challenge is you don't want
to
be in a situation where you do a lot of
work building out a temp table where you
really want to keep that where you want
to keep that information and then as
soon as you lose a session then you know
it's basically it's all all those
queries and everything have to be rerun
but with the temp table
you can even
um i'm pretty sure let's go do this um
so let's do
create index
temp idx on
t
my stuff
uh let's just do name
let's do the id
so now let's go back and see how that
works for us
and so when we do it everything's there
and if we go back and do the call
how far back is that
okay that's back a little bit
there it is
oh
i needed to use tutor
sorry
tutorial
tutorial
there we go
and so it runs through and so this time
it actually created an index in there so
we which again show you can
create multiple tables you can do
foreign keys you can create indexes
there's a lot that you can do
and in particular
again when you're
in a stored procedure these are very
useful because you're probably
depending on what you're doing you may
have some subset of data that you're
trying to build out
and you're wanting to work with that
you're wanting to manipulate that
and it may not be the same use that that
data is in the source database
particularly if you're like a
let's say you've got a transactional
kind of database where you're storing
i don't know
very transactional let's say that you
have a database that's storing credit
card transactions for sales for your
company
and you've got all this data in here and
all it's doing
typically is inserting new transaction
records and so you don't want to have a
lot of indexes on it you don't want to
have a lot of overhead because you want
that insert to just go quickly and be
done you don't want to have to worry
about stuff reindexing and slowing down
particularly as the t table gets large
but
if you want to do a bunch of queries on
that
then of course one thing you can do is
just go create a whole other database
pull that stuff out put your indexes on
things like that but you can also do it
i will say sort of on the fly by
creating a temp table and create that
table pull the data over
set up your indexes
and then go work on the data that way so
work on it from that temp table as
opposed to
your actual table
and you can there are some other things
we're going to see that are
similar approaches to doing this kind of
stuff but a temp table is one of those
that
it's just such an easy way to do
that sort of that sort of work on a
regular basis
particularly within the database
if you're going from a programming
language what you would probably do is
you would do a query you pull the result
set back into your your language into
your application
and then on that you know on that
application side you're going to walk
through it you're going to do your
manipulations or calculations or
whatever it is that you need to do
well what this gives you is an easy way
to build out you know a result set that
you can work with
and now we're going to work look at some
other things we can do as we get further
into
our stored procedures but this one i
really just want to make sure that i
covered temp tables and talked about
those for a bit
because i do think they're going to be
you know useful for us moving forward
and it's just one of those that i
figured also would be a pretty good you
know like one day topic kind of thing
so
you know bottom line don't be afraid to
play around with
commands we've already
learned
you know we've already looked at and
even some of the others within your
stored procedures you can do a lot of
stuff now like i said i could create a
whole table here
there's
it becomes complicated if you're going
to start doing dynamic sql and things
like that but yeah i could have stored
procedures that take parameters and
based on those they go out and build
tables and they go out and do
data loads and all kinds of stuff
particularly if you wanted to somehow
have a way to generate multiple in my
sequel databases that are you know like
make one per customer
stuff like that
if you want to do some sort of
vertical
you know
siloing and stuff like that so you have
one database that you hit and that's
where you get your data you don't have
to worry about
data crossing over from one you know
that's
one group can access that can also
be accessed by somebody else as well
but we get into that's getting into a
lot of i guess some of the more advanced
kinds of things that we're going to talk
about later
most likely if not in this session or
this series than in some of the others
because that does get a little bit more
into
security over just being in a database
that being said
i digress a bit
so i just wanted to point this out
temporary tables they just remember they
last for the session so if you do some
stuff and you're like on that command
line if you're sitting here on a command
line and you create a temporary table
and you quit and come back
it ain't going to be there
and you're gonna have to rebuild it
which again goes back to the whole idea
of you know let's
store this stuff out start a file
somewhere and execute that file or you
know maybe it's a stored procedure so
you can export execute that
uh all within that stored procedure and
then all is good
that being said it's time to wrap it up
for today
keep this i guess shortish
and give you a little bit of time back
as always go out there and have yourself
a great day a great week and we will
talk to you
next time
you