Global temporary table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Global temporary table

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-23 : 00:46:35

Hi,
what im trying to do is to combine multiple SELECT staetment so that it could produce 1 dataset instead of 2(multiple). (fields and column are same, just different tblname). Okay, so im thinking of insert those into temp table.

1. select statement 1 > insert into #MyTemp
2. select statement 2 > i want to insert into #MyTemp too...

Problem is sql disallowed this. That's why im thinking of global temporary table, but dunno how to write tht :P.
Pliz help. Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-23 : 03:13:30

One of the following

1 insert into #MyTemp Select 1
insert into #MyTemp Select 2
2 insert into #MyTemp Select * from (Select 1 union all Select 2) T

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-23 : 20:52:36

normally this would be done with UNION or UNION all, no need for temp tables.

select * from t1
union all
select * from t2

www.elsasoft.org

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-24 : 02:22:01

Yeah Normally Union all or Union Statment with the derived table does the trick for me.


Select * From 
(
	Select 1 as i  
	Union All 
	Select 2 
) As f

Chirag

http://chirikworld.blogspot.com/

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-25 : 19:53:42

thanks all,
just one more question, how do i put WHERE in the block of UNION/UNION ALL? or it is disallowed?? see my eg :-

SELECT *
FROM tblItemDetail
UNION ALL
SELECT *
FROM tblArchiveItemDetail
WHERE (Ownership = 'Gbepb')

this will return all records in the 2 tables, without following the rules Ownership='Gbepb' ?? HELP :)

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-25 : 19:56:41

ok.. so we put the WHRE statement in both SELECT statement.. :P, pheww being beginner is quite exhausting :P

SELECT COUNT(*)
FROM tblItemDetail
WHERE (Ownership = 'Gbepb')
UNION ALL
SELECT COUNT(*)
FROM tblArchiveItemDetail
WHERE (Ownership = 'Gbepb')

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2006-12-25 : 20:04:48

madhivanan,

could u explain what is T in your eg:-

2 insert into #MyTemp Select * from (Select 1 union all Select 2) T

and also, what is better? derived tables OR Temporary tables?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-25 : 21:31:45

T is an alias given to the derived table to avoid writing long names repetitively. Using either derived table or temp table depends upon the size of your data. If you have big amount of data, temp table is preferred else derived one.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources