user1170330 - 1 month ago 4x
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?

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

Source (Stackoverflow)