Great tutorial! Only thing I would add is that temp tables are session or query window specific. If you close your current session the table will not be there as you expect a regular table would. I am always learning so I cant wait to watch more!
Thank you, Alex. You give the quickest easy-to-understand tutoring I have received on my DA journey. You explain these things well, and you do not overload the learner with more than they need per time. One helpful thing I do is practice while I watch and play around with it as you teach. I often break something, though, and that helps too.
Hi Alex, You are so sweet and positive. I been through many of your videos and to be honest I feel hope from you. Seeing you gives hope. Thanks for existing. 🙂
For anyone following along in postgreSQL, use TEMP or TEMPORARY to create your temporary tables. Unfortunately, postgreSQL doesn't recognize the # sign in creating temp tables. EX: CREATE TEMP TABLE temp_employee( employeeid INT, jobtitle VARCHAR(100), salary INT );
Great tutorial, thanks for posting!. One tweak I would suggest - eliminate 'the 'uhs', 'ums', and 'ya knows'. There aren't many, but it's better with none. Just a suggestion, keep up the good work..
I just want to say thank you so much for your video's I have learnt more today than I have ever known about SQL and will continue subscribe. You are a HUGE help!!
You can also check the existence of temporary tables using object explorer. In the object explorer, expand TEMPDB database folder, and then exapand TEMPORARY TABLES folder, and you should see the temporary table that we have created.
Thanks for the good content created for free, it is very useful. I would like to add a little bit of information, if you use one # sign the temp table would be considered local (you can use it only in your currect query window), if you use double ## sign the table would be global (you can use it in a new query window). As they mentioned before, when you close the session the tables will be gone forever, so you need to create them again. My two cents!
Thank you for this video and for all your tutorials. Question: what is the difference between using a temp table to store a part of a table and using a view to store the same logic and the same part of that table?
3:52 if your from table had a 4th column of data (let's say state) and you did "SELECT *" to pull everything would an error result or would that 4th column be ignored?
Yes, there would be an error and what you would need to do is after the #Temp_Employees you would need to do an open parenthesis and specify the columns you're pulling in - Like this: Insert Into #Temp_Tables (column1, column2, column3) Select * From.... Hope that helps!
VARCHAR is best suited for storing short to medium-length strings, while TEXT is better suited for storing large amounts of textual data. the number placed in parentheses after VARCHAR designates the character length allowed in that field, so in the case of this video, the field is restricted to 100 characters in length. Text has some default (but very large) length of possible characters.
Is it advisable to use a #TempTable when importing data in PowerBi for a "DirectQuery". So when Alex has the DROP TABLE IF EXIST... and we would refresh our query, would we get the latest data imported and stored in our #TempTable or would PowerBI not even be able to bring back results? Thanks and I am getting more from this free course than my paid subscriptions! Great job Alex!
These tutorials are amazing! Thanks so much, Alex. Question: Why would a person use a temp table if it is just session-specific. Why wouldn't one just create a new table using a past query so that it is stored permanently, for use later?
Thank you but I don't understand the advantage of temp tables. Would ot using queries without them be faster? You just added more steps. This is my first time seeing a video regarding temp tables btw.
Incredible video!! I did have an error message creating #temp_Employee2 but I see a column was missing for EmployeesPerJob COUNT( and deleted that part of the script and it ran correctly! Wow I love this stuff
How do i improve the skill of knowing different ways to write a code for the same output. Does learning python improves vocabulary of coding? I am aspiring data analyst and i want to master this skill of yours? plz reply....u speak simple and clear in ur videos. Thanks.
Hi Alex, how did you populate the table #temp_Employee using the command...Select * From SQLTutorial..EmployeeSalary? It seems very practical but I don't know where those values came from!
Depends on how you use them, but typically you're not using them as a beginner for the most part. I use them in a lot of SP and more advanced queries so I would consider the actual implementation of Temp Tables in your tool kit as more advanced.
What is the difference between materialized view and a temp table then? I had this qiestion after watching this video. I know materialized view also allows us to do the same thing and improve the processing by retrieving already stored query and the result and make it quicker to retrieve the data.
Hi Great tutorial. I have question about dropping table. I understand that it is done not to get an error. But why to again and again create and insert data into temporary table since we are using it to reduce calculation load. If we do it over and over again are we not adding unnecessary calculation load? Why, once the temporary table is created and filled, don't we rather delete or comment out those CREATE and INSERT INTO queries? Thanks Michal
3:47 not quite sure, but could someone clarify what “SQLTutorial..” is referencing in the FROM clause here? I understand “EmployeeSalary” is the table we’re storing into the temp table, but “SQLTutorial..” seems kinda random - I’m assuming this is the name of the database “EmployeeSalary” is from but was it necessary to add that? Thanks anyone,
SQLTutorial is the name of the database, so if you were in another database named SQLPractice, but had a table that was present in the SQLTutorial database that you wanted to access. Then you would have to use SQLTutorial along with the table name in order to access that table, even if you are in the SQLPractice database. Basically it is allowing you to use tables from different databases, while in another database that doesn't contain that table. So I'm in SQLPractice database, but want to access EmployeeSalary Column, then I'd do SELECT ____ FROM SQLTutorial..EmployeeSalary Hope that made some sense haha, have a great day!
Thanks much alot Whenever I created and inserted data in temp table and shut down my computer and come back and select the temp table it doesn’t work I have to Crete and insert datas again and again when ever I come back into it. Can you please give me some solution for me Thanks
How to insert values in cells in a table in SQL where first column is filled with values while the 2nd and 3rd columns are not?(eg: employeeid, age, jobtitle are the three columns, employeeid is filled with values like 100 to 300 while the other 2 columns are empty and has to be filled, will it have to be done one by one or is there any other way?)
I would like to ask a question. is there any book that you suggest to read for SQL Server? I want a book with full 100% documentation and to learn in deep SQL server.
I'm a college student, and I recently decided to pursue a career as a data analyst. I don't know anyone in that particular field, is there any way I can get in contact with you?
Alex often uses the expression to hit sth. off of sth. What does it mean? To copy? To extract? I tried to google this expression, but couldn't find anything that would apply to this context. Can anyone help?
if OBJECT_ID('tempdb..#temp_Employee') is not null drop table #temp_Employee create table #temp_Employee ( EmployeeID int, JobTitle varchar(100), Salary int ) Select * From #temp_Employee insert into #temp_Employee values ( '1001', 'HR', '45000' I don't know why I am not able to insert values ?
why am i facing issue in creating a temp table CREATE TABLE #temp_Employee ( EmployeeID int, JbbTitle varchar(100), Salary int ) where input is this And output is that ERROR: syntax error at or near "#" LINE 1: CREATE TABLE #temp_Employee ( But its running smoothly when I am using this as an input CREATE TEMPORARY TABLE temp_table ( EmployeeID int, JobTitle varchar(100), Salary int )
"Literally the only difference between a temp table and a regular table is the #" ? I think you meant to say.. Its the only difference in the definition of the two types of tables.. Temp tables live in TEMPDB Unlike physical regular tables... Temp tables dont scale very well with volumes of data.. compared to regular tables...
Temp tables can scale just as well as ordinary tables as long as you’ve setup your SQL correctly. They’re just temporary. They also live in tempdb only when they get above a certain size and need to be flushed to disk.
I don't understand why you didn't demonstrate the easy way to create a temp table. The INTO statement. You could have created a temp table without the pain of the create statement. select * into #emp from Employee where age =30 This would give you a complete temp table without out having to set up a create statement every time.
Because you want to start with an empty table. If you don’t drop the database the next insert will be inserting into a table with records in it already 🤔
Great tutorial! Only thing I would add is that temp tables are session or query window specific. If you close your current session the table will not be there as you expect a regular table would. I am always learning so I cant wait to watch more!
Good note to add - thanks for watching!
Thanks, this is the one piece of information I needed. The difference to a regular table.
Thank you, Alex. You give the quickest easy-to-understand tutoring I have received on my DA journey. You explain these things well, and you do not overload the learner with more than they need per time.
One helpful thing I do is practice while I watch and play around with it as you teach. I often break something, though, and that helps too.
Hi Alex,
You are so sweet and positive. I been through many of your videos and to be honest I feel hope from you. Seeing you gives hope. Thanks for existing. 🙂
Glad to exist :D Thanks for watching! I appreciate it!
Such a great example. I was looking for a solution for something I’m working on at work and this is ‘drop table is exist’ is perfect. Thank you.
For anyone following along in postgreSQL, use TEMP or TEMPORARY to create your temporary tables. Unfortunately, postgreSQL doesn't recognize the # sign in creating temp tables.
EX:
CREATE TEMP TABLE temp_employee(
employeeid INT,
jobtitle VARCHAR(100),
salary INT
);
Thank you! Xampp mysql doesn't recognize the # sign this is really a big help.
In MySQL used CREATE TEMPORARY TABLE
thanks , the syntax is not always the same , the trick is to google or ask chat gpt, the proper syntax for each sql , haha
god bless your soul
@@castilloerickae.8545 try using @
Liking the video even before start watching it.
That's the faith I have on Alex.
Wow! I love the "DROP TABLE IF EXISTS..." command. Really useful too! Thanks Alex. You're GREAT!
when i make it tell me temp is areade in database any solution ?
Great tutorial! This is like querying a query. I’m learning a lot from your videos. Thank you Alex
Once again a great tutorial!! This is the next concept in the Google data analytics course. Thank you so much you made it smooth digestible.
Wow. Never even thought about this. Would make that information super accessible quickly
Great tutorial, thanks for posting!. One tweak I would suggest - eliminate 'the 'uhs', 'ums', and 'ya knows'. There aren't many, but it's better with none. Just a suggestion, keep up the good work..
I just want to say thank you so much for your video's I have learnt more today than I have ever known about SQL and will continue subscribe. You are a HUGE help!!
I got exactly the information I was looking for and I started following your channel, thanks.
You can also check the existence of temporary tables using object explorer. In the object explorer, expand TEMPDB database folder, and then exapand TEMPORARY TABLES folder, and you should see the temporary table that we have created.
I'm preparing for a interview, very helpful video. Thank you
No need to drop #temp table if you close the current session it will automatically delete from the tempdb 😊
Alex Thanks man! All the way from South Africa
Muchas gracias señor Alex 🤠
Simple and functional as always, thanks Alex, data loves you! :)
Ten minutes goes by fast. This was much needed.
Thanks for the good content created for free, it is very useful. I would like to add a little bit of information, if you use one # sign the temp table would be considered local (you can use it only in your currect query window), if you use double ## sign the table would be global (you can use it in a new query window). As they mentioned before, when you close the session the tables will be gone forever, so you need to create them again. My two cents!
Awesome content! Learning a ton!
Nitro shot of knowledge to the brain just when I needed it 💯
Thanks for this Alex!
Helpful video, especially the DROP TABLE tip. Thank you sir
Great helpful tutorials.
Thanks!! Can you make a video explaining schemas?
Thank you for this video and for all your tutorials. Question: what is the difference between using a temp table to store a part of a table and using a view to store the same logic and the same part of that table?
Amazing tutorial as always :)
Hello Alex, Could you please explain the difference between Temp tables and CTEs. Also, when should you use which one?
CTE are only created in memory however temp table can be used multiple times, as long as you use DROP TABLE F EXIST
Thanks, really helfpul.
good explanation, thanks
Great video tutorial
Yes, I like this video 😀
Thank you!
3:52 if your from table had a 4th column of data (let's say state) and you did "SELECT *" to pull everything would an error result or would that 4th column be ignored?
Yes, there would be an error and what you would need to do is after the #Temp_Employees you would need to do an open parenthesis and specify the columns you're pulling in - Like this:
Insert Into #Temp_Tables (column1, column2, column3)
Select *
From....
Hope that helps!
Wow, love this!
Thank you Alex.
Great thanks 👍👌
Hi Alex, kindly explain the difference between 'text' and 'varchar' and why you have to use a parenthesis right after the varchar?
VARCHAR is best suited for storing short to medium-length strings, while TEXT is better suited for storing large amounts of textual data. the number placed in parentheses after VARCHAR designates the character length allowed in that field, so in the case of this video, the field is restricted to 100 characters in length. Text has some default (but very large) length of possible characters.
Its a very interesting idea
Thanks Alex
Very useful trick..Thank you so much
Alex would you consider a one on one training?
Gold content!
Thanks a lot!
Is it advisable to use a #TempTable when importing data in PowerBi for a "DirectQuery". So when Alex has the DROP TABLE IF EXIST... and we would refresh our query, would we get the latest data imported and stored in our #TempTable or would PowerBI not even be able to bring back results? Thanks and I am getting more from this free course than my paid subscriptions! Great job Alex!
super, thank you
Nice and easy thanks
These tutorials are amazing! Thanks so much, Alex. Question: Why would a person use a temp table if it is just session-specific. Why wouldn't one just create a new table using a past query so that it is stored permanently, for use later?
Good day Ms.carrie, we same question, have you know already how to find Temp table?
Hi Alex your tutorial are game-changing for me. can we combine a temporary table and cte
I have been using mysql, but when I use drop table if exists, it shows error in my syntax,
Is there any other way to query for my sql
Shoot, this is genius! Computational efficiency.
Hey Mike!
Great content, your SQL videos are easy to go for quick brush up or interview preparation. Keep it coming
Kudos from india🔥
Geat tutorial. Please, what do the two dots (..) between the table and column mean? Thanks for the video!
Hi, its using to select table from related database.
Thanks for this. Do you ever plan to make tutorials at beginner level?
I actually already have a beginner and intermediate series :)
@@AlexTheAnalyst Ohh. Will check it out then
thank You Sr
Thank you but I don't understand the advantage of temp tables. Would ot using queries without them be faster? You just added more steps. This is my first time seeing a video regarding temp tables btw.
Excellent stuff, can you just do something for stored procedures,if I say what's the best way to handle sp in sql
For sure - I will definitely be doing a video on that as part of this series :)
@@AlexTheAnalyst one thing more,about sub queries
Incredible video!! I did have an error message creating #temp_Employee2 but I see a column was missing for EmployeesPerJob COUNT( and deleted that part of the script and it ran correctly! Wow I love this stuff
How do i improve the skill of knowing different ways to write a code for the same output. Does learning python improves vocabulary of coding? I am aspiring data analyst and i want to master this skill of yours? plz reply....u speak simple and clear in ur videos. Thanks.
Hi Alex, how did you populate the table #temp_Employee using the command...Select * From SQLTutorial..EmployeeSalary? It seems very practical but I don't know where those values came from!
what other advanced tutorials can we expect?
Sys tables, exporting data, stored procedures, and more :)
When i should use a cte, temp table or regular table? I mean, in which scenario fits each one better than other
love your tutorial Alex. I just don't get the use of temp tables as well as ctes. Like why?
Temp tables is considered advanced sql? I think I'm shorting myself when it comes to how I describe my self and where I am sql wise
Depends on how you use them, but typically you're not using them as a beginner for the most part. I use them in a lot of SP and more advanced queries so I would consider the actual implementation of Temp Tables in your tool kit as more advanced.
Это лучшее видео на свете
What is the difference between materialized view and a temp table then? I had this qiestion after watching this video. I know materialized view also allows us to do the same thing and improve the processing by retrieving already stored query and the result and make it quicker to retrieve the data.
Hi Great tutorial. I have question about dropping table. I understand that it is done not to get an error. But why to again and again create and insert data into temporary table since we are using it to reduce calculation load. If we do it over and over again are we not adding unnecessary calculation load? Why, once the temporary table is created and filled, don't we rather delete or comment out those CREATE and INSERT INTO queries? Thanks Michal
Nice
Hey Alex, what is the difference between "Ultimate Beta Course Bundle" to ""Lifetime Access to Analyst Builder"?
3:47 not quite sure, but could someone clarify what “SQLTutorial..” is referencing in the FROM clause here? I understand “EmployeeSalary” is the table we’re storing into the temp table, but “SQLTutorial..” seems kinda random - I’m assuming this is the name of the database “EmployeeSalary” is from but was it necessary to add that?
Thanks anyone,
SQLTutorial is the name of the database, so if you were in another database named SQLPractice, but had a table that was present in the SQLTutorial database that you wanted to access. Then you would have to use SQLTutorial along with the table name in order to access that table, even if you are in the SQLPractice database. Basically it is allowing you to use tables from different databases, while in another database that doesn't contain that table.
So I'm in SQLPractice database, but want to access EmployeeSalary Column, then I'd do SELECT ____ FROM SQLTutorial..EmployeeSalary
Hope that made some sense haha, have a great day!
Any recommendations for certifications on Tableau on Coursera??
You know what's magical / lifesaving? Putting a query result in a temp table.
Wait so you drop and create a table on each query? Or how is the data updated? Why wouldn't you just use views?
Done
Thanks much alot
Whenever I created and inserted data in temp table and shut down my computer and come back and select the temp table it doesn’t work I have to Crete and insert datas again and again when ever I come back into it.
Can you please give me some solution for me
Thanks
Shall we add multiple rows from multiple tables into temp table, if column names are same in temp table ?
How to insert values in cells in a table in SQL where first column is filled with values while the 2nd and 3rd columns are not?(eg: employeeid, age, jobtitle are the three columns, employeeid is filled with values like 100 to 300 while the other 2 columns are empty and has to be filled, will it have to be done one by one or is there any other way?)
When I inserted values, I got two the same rows while I did the same as you. Do you know why two same rows has been created for me (EmployeeID: 1001)?
I would like to ask a question. is there any book that you suggest to read for SQL Server? I want a book with full 100% documentation and to learn in deep SQL server.
Hello Alex, thanks for these videos. How can I delete duplicates on my table?
use distinct when you select your table
You could check out the Row_Number() function
I'm a college student, and I recently decided to pursue a career as a data analyst. I don't know anyone in that particular field, is there any way I can get in contact with you?
Sure thing - you can message me on LinkedIn - I have my link on my UA-cam page on my banner :)
@@AlexTheAnalyst Will do! Thank you for responding, I look forward to contacting you soon.
what do you mean when you use the expression hit off ??? is very confusing
hey Alex can you help me find a platform where i can practise these queries on different tables other than creating them.
can you explain the meaning of varchar? i get the int as integer type of data, but what is varchar? why not put string?
Where can I find the temp table
Alex often uses the expression to hit sth. off of sth. What does it mean? To copy? To extract? I tried to google this expression, but couldn't find anything that would apply to this context. Can anyone help?
can you provide a timestamp of where it was used?
does it show up in the object explorer
Can anyone explain what the number is we put inside parenthese in fron of VARCHAR?
how do i open this console to write words u
I don't think # is a pound sign. I call it a hash sign. :)
What would happen if groupby columns names were different than the tem_table column names?
if OBJECT_ID('tempdb..#temp_Employee') is not null
drop table #temp_Employee
create table #temp_Employee (
EmployeeID int,
JobTitle varchar(100),
Salary int
)
Select *
From #temp_Employee
insert into #temp_Employee values (
'1001', 'HR', '45000'
I don't know why I am not able to insert values ?
The first use case is not really straightforward to me. Can someone point me to a different example that I might find more clear please?
why am i facing issue in creating a temp table
CREATE TABLE #temp_Employee (
EmployeeID int,
JbbTitle varchar(100),
Salary int
)
where input is this And output is that
ERROR: syntax error at or near "#"
LINE 1: CREATE TABLE #temp_Employee (
But its running smoothly when I am using this as an input
CREATE TEMPORARY TABLE temp_table (
EmployeeID int,
JobTitle varchar(100),
Salary int
)
What about NoSQL?
NoSQL is awesome - I may do a video comparing SQL and NoSQL sometime.
(drop table if exists ) doesn't work in sql 2014?!
"Literally the only difference between a temp table and a regular table is the #" ? I think you meant to say.. Its the only difference in the definition of the two types of tables.. Temp tables live in TEMPDB Unlike physical regular tables... Temp tables dont scale very well with volumes of data.. compared to regular tables...
Absolutely correct - only difference in the syntax is what I meant
Temp tables can scale just as well as ordinary tables as long as you’ve setup your SQL correctly. They’re just temporary.
They also live in tempdb only when they get above a certain size and need to be flushed to disk.
I don't understand why you didn't demonstrate the easy way to create a temp table. The INTO statement. You could have created a temp table without the pain of the create statement.
select * into #emp from Employee where age =30
This would give you a complete temp table without out having to set up a create statement every time.
Why not to use CREATE TABLE IF NOT EXISTS instead of dropping it and re-creating it again ?
Because you want to start with an empty table.
If you don’t drop the database the next insert will be inserting into a table with records in it already 🤔