Is this possible?

I have a stored procedure and I want to do something like:
INSERT INTO @.Table
(Field1, Field2, Field3, ...FieldN)
SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
FROM @.Table2
WHERE n is null
SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
FROM @.Table2
WHERE x < 50
SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
FROM @.Table2
WHERE x >= 50
Can I somehow do this? Since I couldn't return the result sets from my
3 selects, I combined them into one result set.
Right now I have a giant UPDATE statement, but it seems really
unwieldy...it's something like:
UPDATE @.Table
SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
There has to be a better way. (Can you tell I only half know what I'm
doing?)
Thank you!union your selects together
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Do:
SELECT SUM( CASE WHEN n IS NULL THEN a END ) AS "Field1",
SUM( CASE WHEN n IS NULL THEN b END ) AS "Field2",
SUM( CASE WHEN n IS NULL THEN c END ) AS "Field3",
SUM( CASE WHEN x < 50 THEN a END ) AS "Field4",
SUM( CASE WHEN x < 50 THEN b END ) AS "Field5",
SUM( CASE WHEN x < 50 THEN c END ) AS "Field6",
SUM( CASE WHEN x >= 50 THEN a END ) AS "Field7",
SUM( CASE WHEN x >= 50 THEN b END ) AS "Field8",
SUM( CASE WHEN x >= 50 THEN c END ) AS "Field9"
FROM Table2 ;
Anith|||"Confused" <cschanz@.gmail.com> wrote in message
news:1135287913.453858.252290@.g49g2000cwa.googlegroups.com...
>I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>
See the following example. Of course you would also replace the word "field"
with "column" because if you even half knew what you were doing then you'd
know that a column isn't a field. :-)
INSERT INTO @.Table
(field1, field2, field3, field4, field5, field6, field7, field8, field9)
SELECT
SUM(CASE WHEN n IS NULL THEN a END) AS field1,
SUM(CASE WHEN n IS NULL THEN b END) AS field2,
SUM(CASE WHEN n IS NULL THEN c END) AS field3,
SUM(CASE WHEN x < 50 THEN a END) AS field4,
SUM(CASE WHEN x < 50 THEN b END) AS field5,
SUM(CASE WHEN x < 50 THEN c END) AS field6,
SUM(CASE WHEN x >= 50 THEN a END) AS field7,
SUM(CASE WHEN x >= 50 THEN b END) AS field8,
SUM(CASE WHEN x >= 50 THEN c END) AS field9
FROM @.Table2 ;
David Portas
SQL Server MVP
--|||Use a case statement to populate Table2.
select <Other Columns>, sum(case when n is null then a else 0 end) as
Field1,
sum(case when n is null then b else 0 end) as Field2,
sum(case when n is null then c else 0 end) as Field3,
sum(case when x < 50 then a else 0 end) as Field4,
sum(case when x < 50 then b else 0 end) as Field5,
sum(case when x < 50 then c else 0 end) as Field6,
sum(case when x >= 50 then a else 0 end) as Field7,
sum(case when x >= 50 then b else 0 end) as Field8,
sum(case when x >= 50 then c else 0 end) as Field9
from Table2
group by <Other Columns>|||ok - i'm not even waiting for my other post to get out there - ignore it
- incomplete
insert into @.table (Field1, ..., Field9)
select
sum(case when n is null then a end) as Field1,
sum(case when n is null then b end) as Field2,
sum(case when n is null then c end) as Field3,
sum(case when x<50 then a end) as Field4,
sum(case when x<50 then b end) as Field5,
sum(case when x<50 then c end) as Field6,
sum(case when x>=50 then a end) as Field7,
sum(case when x>=50 then b end) as Field8,
sum(case when x>=50 then c end) as Field9
from @.Table2
another possibility is to change your table1 to have a criteria
indicator and fewer columns, and union the queries together, e.g.
insert into @.Table (criteria, Field1, Field2, Field3)
select 'null n', sum(a), sum(b), sum(c)
from @.table2
where n is null
union all
select 'x>50', sum(a), sum(b), sum(c)
from @.table2
where x>50
union all
select 'x<=50', sum(a), sum(b), sum(c)
from @.table2
where x<=50
Confused wrote:
> I have a stored procedure and I want to do something like:
> INSERT INTO @.Table
> (Field1, Field2, Field3, ...FieldN)
> SELECT sum(a) AS Field1, sum(b) AS Field2, sum(c) AS Field3
> FROM @.Table2
> WHERE n is null
> SELECT sum(a) AS Field4, sum(b) AS Field5, sum(c) AS Field6
> FROM @.Table2
> WHERE x < 50
> SELECT sum(a) AS Field7, sum(b) AS Field8, sum(c) AS Field9
> FROM @.Table2
> WHERE x >= 50
> Can I somehow do this? Since I couldn't return the result sets from my
> 3 selects, I combined them into one result set.
> Right now I have a giant UPDATE statement, but it seems really
> unwieldy...it's something like:
> UPDATE @.Table
> SET Field1 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field2 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field3 = SELECT sum(a) FROM @.Table2 WHERE n is null,
> SET Field4 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field5 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field6 = SELECT sum(a) FROM @.Table2 WHERE x < 50,
> SET Field7 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field8 = SELECT sum(a) FROM @.Table2 WHERE x >= 50,
> SET Field9 = SELECT sum(a) FROM @.Table2 WHERE x >= 50
> There has to be a better way. (Can you tell I only half know what I'm
> doing?)
> Thank you!
>|||Oops...I forgot to put that...I did try that. And when I do that I get
the following error:
The select list for the INSERT statement contains fewer items than the
insert list. The number of SELECT values must match the number of
INSERT columns.|||Thank you everyone for the input and help! It is much appreciated!

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Fie

Many thanks David and Razvan, I've rated your posts as helpful.
--
Adam J Warne, MCDBA
"David Portas" wrote:

> Not necessarily. We don't know what other columns are involved or what
> the keys are. You can only guarantee that you'll get the same
> particular row from the top of the second query if the three ORDER BY
> clolumns are UNIQUE. Use TOP 1 WITH TIES if the ORDER BY criteria isn't
> unique.
> --
> David Portas
> SQL Server MVP
> --
>>The WITH TIES clause is new to me should be
There is even a web log named toponewithties :)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131539036.434912.103710@.g44g2000cwa.googlegroups.com...
> Thanks for the replies. The WITH TIES clause is new to me should be
> very helpful. Thanks.
>