Is this possible?

22 Dec 2005 9:45 PM

Confused

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!

22 Dec 2005 9:52 PM

Trey Walpole

union your selects together

Confused wrote:
Show quote

> 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!
>

22 Dec 2005 9:59 PM

Gary Gibbs

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>

22 Dec 2005 10:01 PM

Confused

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.

22 Dec 2005 9:53 PM

Anith Sen

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

22 Dec 2005 9:54 PM

David Portas

Show quote

"Confused" <csch***@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
--

22 Dec 2005 10:01 PM

Trey Walpole

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:
Show quote

> 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!
>

22 Dec 2005 10:15 PM

Confused

Thank you everyone for the input and help! It is much appreciated!