Some of the SQL queries may require a temporary holding place in the memory for doing some logical process.
For example, select certain number of rows from a table and remove/update some rows within resultset based on some condition. This scenario may require a temporary variable or table in SQL server to handle the data and produce desired result.
Here are the few ways to store the data in temp tables for processing
1) Temporary Table Variables
It is a good idea to create a temporary variable if the data rows you are going to store is less than few 100 rows.
This has some limitations like cannot create index etc…
example of a temporary table variable use
declare @tempTable TABLE(sno int identity,name varchar(55));
Insert into @tempTable(name) values (‘james’);
Insert into @tempTable(name) values (‘peter’);
Insert into @tempTable(name) values (‘Rusell’);
select * from @tempTable;
2) Temporary Tables
If you need to store a large data to a temp location, it is good idea to store this in a temporary table rather than on a table variable.
example for creating a temporary table
CREATE TABLE #tempTable(sno int identity,name varchar(55));
Insert into #tempTable(name) values (‘james’);
Insert into #tempTable(name) values (‘peter’);
Insert into #tempTable(name) values (‘Rusell’);
select * from #tempTable;
If you want to create a global temporary table which can be accessed across the connections, use ## to create this.
3) Derived Tables
You can use a derived table to hold data temporarily in SQL server.
Derived table is a result set of a subquery.
SELECT sno,name FROM (SELECT * FROM people WHERE age>20) AS temppeople
This derived table temppeople can be used only once in a query.
4) Common Table Expressions (CTE)
CTE is a temporary result set object which can be used the duration of the query.
select sno,name from testtable where name like ‘A%’
Select * from TempCTE
CTE can be used for buliding the recursive query and store the result in one result set.
A CTE can be used many times in same query.
SELECT * FROM people INNER JOIN TempCTE ON people.sno= TempCTE.Sno
leftjoin peoplerol ON peoplerole.sno= TempCTE.Sno
Please post your comments about this article here.