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!