how do I get two resultsets into one temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - General SQL Server

how do I get two resultsets into one temp table?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-16 : 08:39:01

awfdml writes "Need a little help ...i am trying to create a temp table with a result set from a record query that yields, a two column multiple row resultset.

I would like to obtain the second column from the first result set and make that the first row in the first column of my temp table, then the first column from the second result set and make that the first row in the second column of my temp table, etc.

I.E

resultset:

color l number
green l 0
blue l 1
red l 2
black l 3

TO #temptable

A l B
0 l blue
1 l red
2 l black

I have tried to do this

but yielded :

A l B
0 l NULL
1 l NULL
2 l NULL
NULL l blue
NULL l red
NULL l black

Not exactly the results I need I would GREATLY appreciate any help!!!

Here is the exact syntax of what i am trying to do:

I have this in a stored procedure that passes a datetime and room:

create table #table (starttime int, endtime int)
insert #table (starttime)
SELECT end_time
FROM times
WHERE type= 'R'
and room = @room
and start_date= @sdate
and end_time <> 1440
ORDER BY start_date, end_date, start_time, end_time

insert #table (endtime)
SELECT start_time
FROM times
WHERE type = 'R'
and room = @room
and start_date = @sdate
and start_time <> 0
ORDER BY start_date, end_date, start_time, end_time
select * from #table
drop table #table

The problem may stem from the fact that I am doing two seperate inserts into the temp table but I am not sure how to do one insert with 2 different select statements.

I would GREATLY appreciate it if someone can help me! Ive been looking at this for 3 days :( THANKS!"

gpl
Posting Yak Master

195 Posts

Posted - 2004-09-16 : 09:25:54

What does your table look like ?

Im thinking you either want to UNION ALL your 2 resultsets, or - update the existing values with your 2nd set

Or -- we could go for broke and do it all in 1 go, but Id need to see the table structure and a little explanation of what your business rules are (eg end_time <> 1440, what does that mean ?)

Graham

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-09-16 : 09:54:06

Not sure but you could try this:


SELECT	a.end_time, b.start_time
FROM	times a JOIN times b
	  ON 	a.type = b.type
	  AND	a.room = b.room
	  AND	b.start_time <> 0
WHERE 	a.type= 'R'
AND	a.room = @room
AND	a.start_date= @sdate
AND	a.end_time <> 1440

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 10:18:12

Yes, I like the concept of JOINS drymchaser,
maybe awfdml could look into that ;)
...can be found in the SQL manual....

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources