THANK YOU~KUMAR
Actually,this job is more complicate.I need to look up the ao_code by branch_nbr and weight
Table 1 has fields: branch_nbr ,person_id
Table 2 has fields:branch_nbr,ao_code,weight
Table 3 has fields:branch_nbr ,person_id,ao_code
for example:
Table1
branch_nbr person_id
________________________
200 19631025PI
200 A101160444
200 A103291755
200 A102040049
200 A103579754
200 A104066827
200 A104273377
200 A111209967
200 A120839464
306 H121599635
306 G221549430
306 F224525208
306 F221589880
306 F220783124
306 F220751819
306 F123168190
306 F122215949
306 F120493383
306 E222705529
306 A226688641
306 Y200025116
306 T221886536
306 R221636776
306 R122089895
Table 2
branch_nbr ao_code weight portion
_______________________________
200 901 1 1/6
200 902 2 2/6
200 903 3 3/6
306 909 1 1/3
306 907 2 2/3
use branch_nbr as key to lookup table 2 and caculate the portion by weight to assignt the match ao_code and insert the result to Table 3.
According to Table2,I should assign 1/6 of the person_ids at branch_nbr 200 to 901,2/6 of the person_ids at branch_nbr 200 to 902 ,3/6 of the person_ids at branch_nbr 200 to 903 and so on for branch_nbr 306.
From Kumar's solution, I need to set @INROWNUM to 0 when the job switch to assign another branch_nbr.
How can I check branch_nbr of the current row is different from the last row and set @INROWNUM to 0?
Gwen