| Author |
Topic |
|
mason
Starting Member
11 Posts |
 Posted - 2003-06-03 : 16:11:02
|
| Bit of a beginner, but trying to do the following:I need to create a stored procedure that will return all records from two tables as one record set. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 16:16:38
|
| Are there join fields between the two tables?if notselect *, 't1'from tbl1 left outer join tbl2 on 1=0union allselect *, 't2'from tbl1 right outer join tbl2 on 1=0==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/03/2003 16:20:25 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:16:39
|
| Do the two tables have the same layout? If so,CREATE PROC usp_SampleProcASSELECT Column1, Column2, etc...FROM Table1UNION ALLSELECT Column1, Column2, etc...FROM Table2RETURNTaraEdited by - tduggan on 06/03/2003 16:23:03 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:17:38
|
| Nigel, why is a join used in your example? Won't my example work?Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 16:20:08
|
| I just cater for the table structures being different.It should have a union all though instead of union.First time I've ever found a use for a right outer join.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/03/2003 16:21:28 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:22:20
|
quote:
I just cater for the table structures being different.It should have a union all though instead of union.
Ah, thanks for in the info. I'll update my example.Tara |
|
|
|
mason
Starting Member
11 Posts |
Posted - 2003-06-03 : 16:23:20
|
| What's with the 1=0?Thanks for all your help. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 16:27:53
|
| So that it doesn't take any rows from the table in the outer part of the join.You get a recordset for which each row has data from one table only.Tara - you don't really need the all as you have the same table structure (although it would be faster and would depend on whether any duplicates were required). My example may need it as the recordset rows may be over 8060 bytes.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mason
Starting Member
11 Posts |
Posted - 2003-06-03 : 16:29:23
|
| This essentially takes all records from both and encapsulates them into one record set? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-03 : 16:31:12
|
| Yes it encapsulates it into one record set. That is the whole purpose of UNION. More information about UNION can be found in SQL Server Books Online.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 16:33:20
|
| Yep.But why do you want two tables as one recordset?Got the feeling we're answering the wrong question here.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mason
Starting Member
11 Posts |
Posted - 2003-06-03 : 16:36:19
|
| I've been asked to write this as my friend wants everything in one recordset from both tables. I think it's crazy. Thanks for your help. I've got other ones, but I'll post them in their own respective thread. :)Ciao from Canada :) |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-03 : 17:01:54
|
| You never did answer if these tables have a column that joins them? If the answer to that question is yes, then the answers given are not necessarily correct. (They are correct in the fact that they return both tables in one recordset, but is that really what he wants?)-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
Trying to Do The Following