While ... IF... Else

[Kobojunkie]

I am not exactly sure why but the code below does not seem to work right. Anyone with any idea why the while loop below would only process some and not all rows in my table?

My TempProducttable contains about 436 Tables. When I run the code below, I ought to at least have the count in the TProductable at about 436, that is not happening at all. For some reason, it seems my procedure below only inserts some records and completely omits the rest.

Declare @Ttid as integer

Select @Ttid = min(t_tid) from tempproducttable

While @Ttid IS NOT NULL
BEGIN
IF EXISTS( Select * from tempproducttable inner join tproduct on t_code =tp_code and t_tid = @Ttid)
BEGIN
Update tProduct
Set
tp_productcode = t_Productcode
From
tProduct inner join tempproducttable
on t_code = tp_code
and t_tid = @Ttid
END
ELSE
BEGIN
Insert Into tProduct
(tp_code, tp_productcode, tp_groupcode)
select t_code, tempdb_Productcode, t_groupcode
from tempproducttable
Where t_tid = @Ttid
END

Select @Ttid = min(t_tid) from tempproducttable Where t_tid > @Ttid
END -- End while Loop

 

[bborissov]

What you get with this:

While @Ttid IS NOT NULL
BEGIN
IF EXISTS( Select * from tempproducttable
                 inner join tproduct 
                       on t_code =tp_code and t_tid = @Ttid)
    BEGIN
        /*
        Update tProduct
        Set    
            tp_productcode = t_Productcode
        From
            tProduct inner join tempproducttable
                on t_code = tp_code
                and t_tid = @Ttid */
        print 'Update '+CAST(@Ttid as vcarchar(20))
    END
ELSE
    BEGIN
        /*
        Insert Into tProduct
        (tp_code, tp_productcode, tp_groupcode)
        select t_code, tempdb_Productcode, t_groupcode
        from tempproducttable
        Where t_tid = @Ttid*/
        print 'Insert '+CAST(@Ttid as vcarchar(20))
    END
    
    SET  @Ttid = (Select min(t_tid)
                         from tempproducttable
                  Where t_tid > @Ttid)
END -- End while Loop

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.