mulitiple selects in a stored procedure

Hi,
I am trying to create a pagination within a stored procedure but I need
to select from several tables:
First I am inserting the fields into a temp table and this works fine:
INSERT INTO #TempItems (Name)
SELECT Name FROM tblName
I thought I could get data from other tables using UNION:
INSERT INTO #TempItems (Name,Address,Telephone,Street)
SELECT Name FROM tblName
UNION
SELECT Address FROM tblAddress
UNION
SELECT Telephone FROM tblTelephone
UNION
SELECT Street FROM tblStreet
*These are bogus fields I have used as examples.
But the error I get is that the number of insert fields is less then the
select? Could anyone help on how the best way to achieve this?
many thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can google the newsgroups (suggest .programming) for pagination
techniques. Your error is due to a misunderstanding of how union works.
Union merely combines separate result sets into one; you are attempting to
use it (incorrectly) as you would a join. Assuming that a union could be
used, you must specify 4 items within the select list of each select
statement that is part of the union, corresponding to the 4 columns to be
inserted. There are other flaws in your logic, but this should get you
started.
You should be inserting using something like the following
insert ...
select ...
from tblName inner join tblAddress on ...
inner join tblTelephone on ...
inner join tblStreet on ...
where ...
How those joins are made (and their type - inner, outer, cross, etc) I
cannot answer without knowing the relationships between the table.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:OoqGZO3rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to create a pagination within a stored procedure but I need
> to select from several tables:
> First I am inserting the fields into a temp table and this works fine:
> INSERT INTO #TempItems (Name)
> SELECT Name FROM tblName
> I thought I could get data from other tables using UNION:
> INSERT INTO #TempItems (Name,Address,Telephone,Street)
> SELECT Name FROM tblName
> UNION
> SELECT Address FROM tblAddress
> UNION
> SELECT Telephone FROM tblTelephone
> UNION
> SELECT Street FROM tblStreet
> *These are bogus fields I have used as examples.
>
> But the error I get is that the number of insert fields is less then the
> select? Could anyone help on how the best way to achieve this?
> many thanks in advance
> Peter
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Mulitiple DBs

Folk
I have multiple SQL SERVERs over 15 Servers. I have couple of test machines. I don't want to go to every machine and add evry single registration of every servers in mmc. that just way too much work. can i import an mmc console from one machine to another
if yes , how can i do it
Thank
GirishI exported registry keys under
HKEY_CURRENT_USER\Software\Microsoft\MSSQLServer\SQLEW\Registered
Servers X
when I did it last time on MSSQL7
Bart Simpson
Last time
On Tue, 2 Mar 2004 08:36:07 -0800, Girish
<anonymous@.discussions.microsoft.com> wrote:
>Folks
>I have multiple SQL SERVERs over 15 Servers. I have couple of test machines. I don't want to go to every machine and add evry single registration of every servers in mmc. that just way too much work. can i import an mmc console from one machine to another ?
>if yes , how can i do it ?
>Thanks
>Girish

Mulitiple DBs

Folks
I have multiple SQL SERVERs over 15 Servers. I have couple of test machines.
I don't want to go to every machine and add evry single registration of eve
ry servers in mmc. that just way too much work. can i import an mmc console
from one machine to another
?
if yes , how can i do it ?
Thanks
GirishI exported registry keys under
HKEY_CURRENT_USER\Software\Microsoft\MSS
QLServer\SQLEW\Registered
Servers X
when I did it last time on MSSQL7
Bart Simpson
Last time
On Tue, 2 Mar 2004 08:36:07 -0800, Girish
<anonymous@.discussions.microsoft.com> wrote:

>Folks
>I have multiple SQL SERVERs over 15 Servers. I have couple of test machines. I don'
t want to go to every machine and add evry single registration of every servers in m
mc. that just way too much work. can i import an mmc console from one machine to ano
the
r ?
>if yes , how can i do it ?
>Thanks
>Girish