Iterating through a table one row at a time..... 2

[Garreth21]

Hi everyone

I need to go through a table of data and look at 3 specific columns. If the value is greater then 0 for a given column, I need to make a new record in another table.

Here's an example

Table 1
ID Total Bonus A Bonus B Bonus
TEST1 125.25 500 250
TEST2 340 0 100

Table 2
ID TYPE AMOUNT
TEST1 Total Bonus 125.25
TEST2 Total Bonus 340
TEST1 A Bonus 500
TEST1 B Bonus 250
TEST2 B Bonus 100

I can't seem to figure it out. I would like to do it with out cursors if possible. Anyone have any advice on how to go about this?

Thanks in advance.

 

[SQLDenis]

insert into table2
select id,'Total Bonus',[Total Bonus]
from Table1 where [Total Bonus] >0
union all
select id,'A Bonus',[A Bonus]
from Table1 where [A Bonus] >0
union all
select id,'B Bonus',[B Bonus]
from Table1 where [B Bonus] >0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I recommend you use the union all statement to accomplish this.

Select Id,
       'Total Bonus' As Type,
       [Total Bonus]
From   Table1
Where  IsNull([Total Bonus], 0) > 0

Union All

Select Id,
       'A Bonus' As Type,
       [A Bonus]
From   Table1
Where  IsNull([A Bonus], 0) > 0

Union All

Select Id,
       'B Bonus' As Type,
       [B Bonus]
From   Table1
Where  IsNull([B Bonus], 0) > 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Hehe,
George after all those times I can finally say to you: type faster haha
Anyway no need to use ISNULL (IMHO) since IsNull([B Bonus], 0) > 0 can never be greater than 0 and thus will not go into the second table anyway

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

That's why you posted first! You didn't include the IsNull check. And, of course you're right IsNull isn't necessary here.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I posted first because I wasn't as nice as you and did not include the

[code]

block

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/