IF Exists UPDATE ELSE INSERT Problem

Hi,





I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).





I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.





Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I’m not sure how should I go about that problem. This is my stored procedure:





DECLARE @CatNo INT, @CatName varchar(10)


SET @CatNo = 2


SET @CatName = 'bracket'





IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )


BEGIN


UPDATE ProductCategory SET CategoryID = @CatNo


FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid


END


ELSE


BEGIN


INSERT INTO ProductCategory ( ProductID, CategoryID)


SELECT uid, @CatNo FROM Products


WHERE Products.CatName = @CatName


END





SET @CatNo = 3


SET @CatName = 'cable'





IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )


BEGIN


UPDATE ProductCategory SET CategoryID = @CatNo


FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid


END


ELSE


BEGIN


INSERT INTO ProductCategory ( ProductID, CategoryID)


SELECT uid, @CatNo FROM Products


WHERE Products.CatName = @CatName


END


(... Goes for another 37 categories)











Thank you for help.





Kooba