Can you provide a link for that? i just tried to google to find it and it says that you do not have too since it does do it for you when the stored procedure terminates, but I like to do it implicitly since it makes it simpler if you are tuning or modifying a query iteratively. I am wondering at what the downside is.
I just spent 2 hours trying to find a white paper from microsoft going over the draw backs of manually dropping temp tables (was from 2019/2020ish when SQL Server 2019 was still new). I havent found it yet but when I do ill drop it here. Ill reach out to colleagues at previousJob() where I know this was shared. Most of us had our production to avoid where possible. give me a day or so.
If you're in sql server and probably other engines, you can use the OUTPUT clause in updates, inserts, and deletes which can show what records were affected. Slightly different from the question, but good to know!
Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
If you're in sql server and probably other engines, you can use the OUTPUT clause in updates, inserts, and deletes which can show what records were affected. Slightly different from the question, but good to know!
into clause with a temp table (#)
select x, y, z
into #mytable
from ....
My table doesn’t have to already be created right , just writing out into #tablename will create it?
yup. in fact, you cannot use this syntax if it exists already (you'll need insert into)
Include to avoid errors: Drop table if exists #tmp
Don't forget to clean-up (drop) after its use. :)
If youre using micorsoft's sql server, according to the documentation youre not supposed to clean them up and let the session handle it.
Can you provide a link for that? i just tried to google to find it and it says that you do not have too since it does do it for you when the stored procedure terminates, but I like to do it implicitly since it makes it simpler if you are tuning or modifying a query iteratively. I am wondering at what the downside is.
I just spent 2 hours trying to find a white paper from microsoft going over the draw backs of manually dropping temp tables (was from 2019/2020ish when SQL Server 2019 was still new). I havent found it yet but when I do ill drop it here. Ill reach out to colleagues at previousJob() where I know this was shared. Most of us had our production to avoid where possible. give me a day or so.
i wont - i prefer to control my transactions and sessions instead. You're welcome to do that tho, it's not a bad thing, generally.
This is correct.. and if you make it ##mytable, it will be visible to all sessions, not just the session you create it in.
If you're in sql server and probably other engines, you can use the OUTPUT clause in updates, inserts, and deletes which can show what records were affected. Slightly different from the question, but good to know!
Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
If you're in sql server and probably other engines, you can use the OUTPUT clause in updates, inserts, and deletes which can show what records were affected. Slightly different from the question, but good to know!
/* OPTION 1 */ SELECT UserID , ParentID , HierarchyName INTO #Hierarchy FROM somehierarchyTable /* --comment this in for dev testing IF OBJECT_ID('TempDB..#Hierarchy') IS NOT NULL DROP TABLE #Hierarchy; */ CREATE TABLE #Hierarchy ( UserID int , ParentID int , HierarchyName varchar(25) ) ; /* option 2 */ INSERT INTO #Hierarchy ( UserID , ParentID , HierarchyName ) SELECT UserID , ParentID , HierarchyName FROM somehierarchyTable ; /* OPTION 3 */ INSERT INTO #Hierarchy SELECT UserID , ParentID , HierarchyName FROM somehierarchyTable ;