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!"
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 ?)
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
how do I get two resultsets into one temp table?
Forum Locked
Posted - 09/16/2004 : 08:39:01
