|
|
| Article |
fooclass
Junior Member
Articles: 4 Comments: 1
|
|
|
| Posted: Mon Jul 07, 2008 12:07 pm |
|
|
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 this based on some condition.
This may require a temporary variable or table in SQL server 2005 to achieve this task.
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
| Code: | 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
| Code: | 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.
example, ##globaltemtable
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.
Example,
| Code: | | 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.
Example,
| Code: |
with TempCTE(sno,name)
as
(
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.
Example,
| Code: |
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. |
|
|
| Comments |
fooclass
Junior Member
Joined: 01 Apr 2007
Articles: 4
Comments: 1
|
|
|
| |
Page 1 of 1 |
All times are GMT
You cannot post articles in this chapter
You cannot edit your articles in this chapter
You cannot delete your articles in this chapter
You cannot rate articles in this chapter
You cannot post comments in this chapter
You cannot edit your comments in this chapter
You cannot delete your comments in this chapter
You cannot rate comments in this chapter
|
|
