user1170330 user1170330 - 3 months ago 14
SQL Question

Iterating through every row efficient?

Suppose I have the following table

T1
:

| col1 | col2 |
|------|------|
| 0 | 0 | // A++
| 3 | 123 | // B++
| 0 | 5 | // C++
| 8 | 432 | // A++
| 0 | 4 | // B++


I now need to create a trigger (on
INSERT
), that analyses every row, increases a counter (see below), populates the table
T2
with the values of the counter:

IF col1 = 0 AND col2 = 0
A++
ELSE IF col1 = 0 col2 > 0
B++
ELSE IF col1 > 0
C++


In this case,
T2
would look like:

| id | A | B | C |
|----|---|---|---|
| 1 | 1 | 2 | 2 |


My question is more about the design: Should I really iterate through each row, as described HERE, or is there a more efficient way?

Answer

Try something like this in trigger

 ;with data as
 (
SELECT Sum(CASE WHEN col1 = 0 AND col2 = 0 THEN 1 END) AS a,
       Sum(CASE WHEN col1 = 0 AND col2 > 0 THEN 1 END) AS b,
       Sum(CASE WHEN col1 > 0 THEN 1 END) AS c
FROM   (VALUES (0, 0 ),
               (3, 123 ),
               (0, 5 ),
               (8, 432 ),
               (0, 4 ) ) tc ( col1, col2 ) 
               )
UPDATE yt
SET    a = dt.a,
       b = dt.b,
       c = dt.c
FROM   yourtable yt
       JOIN data dt
         ON a.id = b.id 

This does not require row by row iteration. Replace the table valued constructor with Inserted table