SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 how do I get two resultsets into one temp table?
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/16/2004 :  08:39:01  Show Profile  Visit AskSQLTeam's Homepage
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

United Kingdom
195 Posts

Posted - 09/16/2004 :  09:25:54  Show Profile
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
Go to Top of Page

drymchaser
Aged Yak Warrior

USA
552 Posts

Posted - 09/16/2004 :  09:54:06  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/16/2004 :  10:18:12  Show Profile
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 */
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000