Temp Tables - VijayIndia/StoredProcedure GitHub Wiki
1.Different Methods to Create Temporary Table :
Method 1: Create Temporary Table and then Insert into Temporary table from Select Query
Method 2: Select Query to Insert into Temporary Table(Internally temp table gets created and Insert gets applied)
Method 3: Create Temporary Table, Select Query, Iterating resultSet and Store in temporary table
Method 4: Common Table Expression, Select Query (Internally insert happens)
2.Difference between Common Table Expression and Temp table :
| Temp table | CTE |
|---|---|
| Used to store the result of query on temporary bases | Used to Store resultSet of Complex Subquery on a temporary basis |
| It doesn't support Recursive calls | Mainly Used for Recursive calls (using the same table multiple times) |
| Life time is limited to current session (It can be accessed from any other stored procedure within the current session) | Life time is limited to Current Query (Within the Current Stored Procedure) |
| Defined by using '#' | Defined by using 'With' statement |
| Ex Query: select * into #tempTable from MHA,now I can use this temp table out side of this query but with in session | ;with myCTE as (select ParentLevel, ParentID, ChildID from MHA where ChildID = 1 UNION ALL select MHA.ParentLevel, MHA.ParentID, MHA.ChildID from MHA inner join myCTE on MHA.ParentID = myCTE.ChildID where MHA.ParentID <> 0) |