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!
@AlexTheAnalyst3 жыл бұрын
Good note to add - thanks for watching!
@Ohallors2082 жыл бұрын
Thanks, this is the one piece of information I needed. The difference to a regular table.
@AkporHari Жыл бұрын
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.
@rajpreetcheema40753 жыл бұрын
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. 🙂
@AlexTheAnalyst3 жыл бұрын
Glad to exist :D Thanks for watching! I appreciate it!
@user-uu2ph1qi2e Жыл бұрын
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 );
@castilloerickae.8545 Жыл бұрын
Thank you! Xampp mysql doesn't recognize the # sign this is really a big help.
@Zinberry Жыл бұрын
In MySQL used CREATE TEMPORARY TABLE
@nauikun652 Жыл бұрын
thanks , the syntax is not always the same , the trick is to google or ask chat gpt, the proper syntax for each sql , haha
@aucksun11 ай бұрын
god bless your soul
@mohamedhassany59846 ай бұрын
@@castilloerickae.8545 try using @
@SevenRingsofSaturn9 ай бұрын
Wow! I love the "DROP TABLE IF EXISTS..." command. Really useful too! Thanks Alex. You're GREAT!
@OmarMohamed-od9ms9 ай бұрын
when i make it tell me temp is areade in database any solution ?
@shanh542 жыл бұрын
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.
@sanilkumarbarik9151 Жыл бұрын
Liking the video even before start watching it. That's the faith I have on Alex.
@Plus1HD3 жыл бұрын
I'm preparing for a interview, very helpful video. Thank you
@seekerj16432 жыл бұрын
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.
@rachrach9871 Жыл бұрын
Great tutorial! This is like querying a query. I’m learning a lot from your videos. Thank you Alex
@oguzozakn63052 жыл бұрын
I got exactly the information I was looking for and I started following your channel, thanks.
@jacquelineconerly915011 ай бұрын
Ten minutes goes by fast. This was much needed.
@terihandrick23338 ай бұрын
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!!
@sisiphosidiya64853 жыл бұрын
Alex Thanks man! All the way from South Africa
@tristanwait4itlegendary4 ай бұрын
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.
@oscarnests6 ай бұрын
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!
@ahmetalperenyildirim61842 жыл бұрын
Simple and functional as always, thanks Alex, data loves you! :)
@sanjanakale855 Жыл бұрын
Hello Alex, Could you please explain the difference between Temp tables and CTEs. Also, when should you use which one?
@watzjamming Жыл бұрын
CTE are only created in memory however temp table can be used multiple times, as long as you use DROP TABLE F EXIST
@cloudkungfu2 жыл бұрын
Nitro shot of knowledge to the brain just when I needed it 💯
@yon_draper Жыл бұрын
Helpful video, especially the DROP TABLE tip. Thank you sir
@ash19832 ай бұрын
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?
@cleobychristine Жыл бұрын
Hi Alex, kindly explain the difference between 'text' and 'varchar' and why you have to use a parenthesis right after the varchar?
@eddiej1414 Жыл бұрын
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.
@aarthi_navin076 ай бұрын
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
@abhay62769 күн бұрын
No need to drop #temp table if you close the current session it will automatically delete from the tempdb 😊
@tristanwait4itlegendary4 ай бұрын
Its a very interesting idea
@1440MAP9 ай бұрын
Awesome content! Learning a ton!
@carriegoff3030 Жыл бұрын
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?
@NB20079 Жыл бұрын
Good day Ms.carrie, we same question, have you know already how to find Temp table?
@rechouka72872 жыл бұрын
Amazing tutorial as always :)
@anthonyd9043 Жыл бұрын
Thanks for this Alex!
@hsoley2 жыл бұрын
Gold content!
@ResilientFighter7 ай бұрын
Shoot, this is genius! Computational efficiency.
@samose1003 жыл бұрын
Hey Mike! Great content, your SQL videos are easy to go for quick brush up or interview preparation. Keep it coming Kudos from india🔥
@md.mahmudulhasan9272 Жыл бұрын
Great helpful tutorials.
@SEAN-ku5xh5 ай бұрын
Wow, love this!
@romanvasiura67052 жыл бұрын
Yes, I like this video 😀 Thank you!
@rbsorg Жыл бұрын
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!
@anthonynkem2 жыл бұрын
Great video tutorial
@olenaqwerty78952 жыл бұрын
good explanation, thanks
@uwillnevahno68373 жыл бұрын
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?
@AlexTheAnalyst3 жыл бұрын
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!
@YasinKAYA-do4ek2 жыл бұрын
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.
@goldencricketstars3060 Жыл бұрын
Hi Alex your tutorial are game-changing for me. can we combine a temporary table and cte
@kemibello6812 Жыл бұрын
Thanks, really helfpul.
@willymwangi386510 ай бұрын
Thank you Alex.
@shekhark11392 жыл бұрын
Very useful trick..Thank you so much
@businessacademics33343 жыл бұрын
Excellent stuff, can you just do something for stored procedures,if I say what's the best way to handle sp in sql
@AlexTheAnalyst3 жыл бұрын
For sure - I will definitely be doing a video on that as part of this series :)
@businessacademics33343 жыл бұрын
@@AlexTheAnalyst one thing more,about sub queries
@akshaypatil8155 Жыл бұрын
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.
@coteten7 ай бұрын
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!
@ashnahida13 жыл бұрын
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
@AlexTheAnalyst3 жыл бұрын
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.
@mckenzie69734 ай бұрын
love your tutorial Alex. I just don't get the use of temp tables as well as ctes. Like why?
@juanvergara58143 жыл бұрын
When i should use a cte, temp table or regular table? I mean, in which scenario fits each one better than other
@allsocialmonkey18472 жыл бұрын
Geat tutorial. Please, what do the two dots (..) between the table and column mean? Thanks for the video!
@ahmetalperenyildirim61842 жыл бұрын
Hi, its using to select table from related database.
@asiagladden30809 ай бұрын
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
@user-gq2eq4gw2k5 ай бұрын
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.
@SyedShadabHussaini19963 жыл бұрын
Thanks for this. Do you ever plan to make tutorials at beginner level?
@AlexTheAnalyst3 жыл бұрын
I actually already have a beginner and intermediate series :)
@SyedShadabHussaini19963 жыл бұрын
@@AlexTheAnalyst Ohh. Will check it out then
@ezehconfidenceadaeze6189 Жыл бұрын
Thanks Alex
@ghiegomez6 ай бұрын
Alex would you consider a one on one training?
@BCTAHbKA2 жыл бұрын
Это лучшее видео на свете
@alaeddinmsetri4788 Жыл бұрын
Nice and easy thanks
@arcaneknight97993 жыл бұрын
what other advanced tutorials can we expect?
@AlexTheAnalyst3 жыл бұрын
Sys tables, exporting data, stored procedures, and more :)
@mfundohg9582 жыл бұрын
Great thanks 👍👌
@helenasalvetova477510 ай бұрын
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
@shokoofehheidari5345 Жыл бұрын
Thanks a lot!
@arslonboyisorov8452 жыл бұрын
super, thank you
@juanmanuelarevalomadrid19379 ай бұрын
what do you mean when you use the expression hit off ??? is very confusing
@tasleemf22795 ай бұрын
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?)
@nothingtoseehere57606 ай бұрын
Wait so you drop and create a table on each query? Or how is the data updated? Why wouldn't you just use views?
@viaticspace Жыл бұрын
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?
@Kavi-learn Жыл бұрын
can you provide a timestamp of where it was used?
@santiagofajardo4949 Жыл бұрын
thank You Sr
@OmorFarukbablu10 ай бұрын
Hey Alex, what is the difference between "Ultimate Beta Course Bundle" to ""Lifetime Access to Analyst Builder"?
@tristanwait4itlegendary4 ай бұрын
does it show up in the object explorer
@alemabebe56472 жыл бұрын
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
@secretnobody6460 Жыл бұрын
can you explain the meaning of varchar? i get the int as integer type of data, but what is varchar? why not put string?
@shekhark11392 жыл бұрын
Shall we add multiple rows from multiple tables into temp table, if column names are same in temp table ?
@karanjadavid21782 жыл бұрын
You know what's magical / lifesaving? Putting a query result in a temp table.
@maryamnaz576 Жыл бұрын
Can anyone explain what the number is we put inside parenthese in fron of VARCHAR?
@hipeacok Жыл бұрын
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)?
@vasilis70762 жыл бұрын
What would happen if groupby columns names were different than the tem_table column names?
@pabloct32 жыл бұрын
Whats the point of creating a temp table over a regular view? Is it mainly to do with storage efficiencies. Why not just Drop the table when you're down with it? Or is it because temp tables are good for stored procedures because its more efficient? thanks! just trying to figure it out
@pabloct32 жыл бұрын
And when you are doing joins why not just inert into a regular table? whats the point of the temp? memory?
@creativitytools45766 ай бұрын
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 ?
@TravelingWhileBlack360_Espanol3 жыл бұрын
"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...
@AlexTheAnalyst3 жыл бұрын
Absolutely correct - only difference in the syntax is what I meant
@MiningForPies11 ай бұрын
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.
@ekanshmishra45172 жыл бұрын
hey Alex can you help me find a platform where i can practise these queries on different tables other than creating them.
@ChrisMavroeidis2 жыл бұрын
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.
@shamsbehery54327 ай бұрын
(drop table if exists ) doesn't work in sql 2014?!
@mcfunthomas_mc Жыл бұрын
I don't think # is a pound sign. I call it a hash sign. :)
@momohtimothy68987 ай бұрын
Ha, first watch looks difficult, i'll re-watch to understand. I go again.
@NB20079 Жыл бұрын
Where can I find the temp table
@grandetaco44166 ай бұрын
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.
@anshusharma76268 ай бұрын
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 )
@Farheeeezy Жыл бұрын
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?
@nathaliamendesdealbuquerqu34062 жыл бұрын
Hello Alex, thanks for these videos. How can I delete duplicates on my table?
@ysonmencidor68342 жыл бұрын
use distinct when you select your table
@davidi.ezekiel93452 жыл бұрын
You could check out the Row_Number() function
@nickmccluskey94003 жыл бұрын
Any recommendations for certifications on Tableau on Coursera??
@kelthekonqrr Жыл бұрын
Nice
@skylar91053 жыл бұрын
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?
@AlexTheAnalyst3 жыл бұрын
Sure thing - you can message me on LinkedIn - I have my link on my KZfaq page on my banner :)
@skylar91053 жыл бұрын
@@AlexTheAnalyst Will do! Thank you for responding, I look forward to contacting you soon.
@kammelna2 жыл бұрын
I want to retrieve a row by a max value in a specific column… Example: I have location, date, and temperature columns. I want to have max temperature in each location with corresponding date. Anyone can help?
@jyrrin2 жыл бұрын
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,
@Zaqhry2 жыл бұрын
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!
@samuelraj5613 Жыл бұрын
Done
@jamalalkelani4439 Жыл бұрын
Why not to use CREATE TABLE IF NOT EXISTS instead of dropping it and re-creating it again ?
@MiningForPies11 ай бұрын
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 🤔
@amarnadhgunakala29013 жыл бұрын
What about NoSQL?
@AlexTheAnalyst3 жыл бұрын
NoSQL is awesome - I may do a video comparing SQL and NoSQL sometime.
@MannyDelaCruzBOOM4U2 жыл бұрын
Instead of doing DROP TABLE IF EXISTS - what about CREATE TABLE IF NOT EXISTS...?
@MiningForPies11 ай бұрын
You can, but you’d be left with the data from the last run.
@mrsebastianhendricks Жыл бұрын
I normally use drop table if exists #temp, #temp2 For multiple tables, do u know a way to drop more then 3 tables without listing them Eg I have 10 temp tables , Instead of using this syntax drop table if exists #temp, #temp2 , etc Is there a better way to do this?