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

MSSQL Query Memory when using long fields

Here is our problem...

We are doing a lot of selects against a table that has one large field
in it.

If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)

When we run the one without the description column, we can watch
perfmon and see a very quick spike to physical disk. If we add in the
description field we can see that the server becomes I/O bound - the
disk sits at 100% until the query is complete.

We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?

For example:
TMZDIFF int410
WRITETIMEcharno 16
System_Namecharno 64
Timestampcharno16
Name charno32
Mount_Pointcharno32
Size intno4 10
Space_Usedintno410
Space_Availableintno410
Inode_Sizeintno410
Inodes_Usedintno410
Inodes_Freeintno410
Space_Used_Percentintno410
Inodes_Used_Percentintno410
FS_Type charno8
Space_Available_Percentintno410
Name_U ncharno32
Descriptionncharno768(wildfyre53207@.yahoo.com) writes:
> If we do a select against all the fields except for description, the
> query comes back relatively quickly. If we add that last field (768
> chars) to the query, our query takes 10x longer (5 seconds vs 56
> seconds.)

My initial reaction is that there is an index on the table that covers
all columns, save the long column.

Could you post CREATE TABLE and CREATE INDEX statements for the table?
Don't forget PRIMARY KEY and UNIQUE constraints.

> We have tweaked the min query memory setting for the server but it
> seems to have had no effect no matter how high we set it. Is there
> some point at which MSSQL decides it cannot perform the transaction in
> memory? What would I increase to cure this problem?

How many rows are you returning? On what sort of network connection?
How much memory do you have in the machine?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||give us an example of the two selects.