January 27, 2009 at 3:54 pm
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.
January 28, 2009 at 4:46 am
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
January 28, 2009 at 2:42 pm
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?
January 29, 2009 at 4:13 am
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:
January 29, 2009 at 4:30 am
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
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
January 29, 2009 at 6:07 am
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