Help with combining 2 queries

  • I have an insert statement and an update statement as follows:

    INSERT INTO TableA

    (

    ColA1,

    ColA2,

    ColA3

    )

    SELECT

    ColB1,

    SUM(ColB2) ColB2,

    0.00 ColB3

    FROM TableB

    GROUP BY ColB1

    UPDATE A

    SET A.ColA3 = ISNULL(B.ColB3,0.00)

    FROM TableA A,

    (SELECT

    ColB1,

    SUM(ColB3) ColB3

    FROM TableB

    WHERE ColB4 BETWEEN 0 AND 10

    GROUP BY ColB1

    ) B

    WHERE A.ColA1 = B.ColB1

    Is there any way I can combine the 2 statements and run just 1st insert statement? I cannot put a GROUP BY on ColB4 in the insert statement.

  • KB (1/27/2009)


    I have an insert statement and an update statement as follows:

    INSERT INTO TableA

    (

    ColA1,

    ColA2,

    ColA3

    )

    SELECT

    ColB1,

    SUM(ColB2) ColB2,

    0.00 ColB3

    FROM TableB

    GROUP BY ColB1

    UPDATE A

    SET A.ColA3 = ISNULL(B.ColB3,0.00)

    FROM TableA A,

    (SELECT

    ColB1,

    SUM(ColB3) ColB3

    FROM TableB

    WHERE ColB4 BETWEEN 0 AND 10

    GROUP BY ColB1

    ) B

    WHERE A.ColA1 = B.ColB1

    Is there any way I can combine the 2 statements and run just 1st insert statement? I cannot put a GROUP BY on ColB4 in the insert statement.

    You can use a sub-select or even a CTE. Let's see if I get this right. It'll look something like this:

    WITH B AS

    (ColB1

    ,ColB2

    ,ColB3

    FROM TableB

    WHERE ColB4 BETWEEN 0 and 10)

    INSERT INTO TableA

    (

    ColA1,

    ColA2,

    ColA3

    )

    SELECT

    b.ColB1

    ,SUM(b.ColB2)

    ,ISNULL(SUM(B.ColB3),0.00)

    FROM b

    JOIN TableB b2

    ON b.ColB1 = b2.ColB1 --or whatever the key is

    GROUP BY b2.ColB1

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Grant,

    I tried using a subquery as well as CTE, the results for ColA3 are not right.

    Suppose Table B has 4 rows that need to be inserted into Table A. But only 1 row in Table B satifies the ColB4 BETWEEN 0 AND 10 condition.

    In this situation, instead of putting the correct value of SUM(ColB3) in Table A, the query puts 4 times the value of SUM(ColB3) in Table A.

    And the last part of your query

    --------------------------------

    FROM b

    JOIN TableB b2

    -----------------------------------

    is a little confusing. I think b is supposed to be TableB and b2 is supposed to be CTE B, am I making any sense?

  • INSERT INTO TableA

    (

    ColA1,

    ColA2,

    ColA3

    )

    SELECT TB.ColB1,

    TB.ColB2,

    TB.ColB3

    FROM (SELECT

    ColB1,

    (SELECT SUM(ColB2) FROM TableB ) ColB2,

    CASE WHEN ColB4 BETWEEN 0 AND 10 then

    (SELECT SUM(ColB3) FROM TableB )

    ELSE

    ISNULL(ColB3,0.00)

    END AS ColB3

    FROM TableB

    GROUP BY ColB1,ColB2,ColB3,ColB4) TB

    A round about Way though.. :hehe:

  • It looks simple enough to get away with this, but check your performance:

    INSERT INTO TableA

    (ColA1,

    ColA2,

    ColA3)

    SELECT ColB1,

    SUM(ColB2) AS ColB2,

    SUM(CASE WHEN ColB4 BETWEEN 0 AND 10 THEN ColB3 ELSE 0 END) AS ColB3

    FROM TableB

    GROUP BY ColB1

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • KB (1/28/2009)


    Hi Grant,

    I tried using a subquery as well as CTE, the results for ColA3 are not right.

    Suppose Table B has 4 rows that need to be inserted into Table A. But only 1 row in Table B satifies the ColB4 BETWEEN 0 AND 10 condition.

    In this situation, instead of putting the correct value of SUM(ColB3) in Table A, the query puts 4 times the value of SUM(ColB3) in Table A.

    And the last part of your query

    --------------------------------

    FROM b

    JOIN TableB b2

    -----------------------------------

    is a little confusing. I think b is supposed to be TableB and b2 is supposed to be CTE B, am I making any sense?

    Sorry, that last bit was a typo. Since we're talking TableA, whatever, without structures, it's hard to syntax check everything.

    You should be able to aggregate and filter. You can filter and then join to aggregate or you can aggregate and then join to filter, but one of them will work. If neither is working, you might need to reassess the structures or the logic or both.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply