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.

KoobaWhat kind of error message returned?
Check if there are unique index? or referencial integratiies ?

If you insert Identity column, make sure turn on IDENTITY_INSERT.|||Do you actually have that exact same code 39 times? That's not good. You can cut out all of that excess code with a few smart statements and a table with your category names.

Example:

Create a table:
Cat (CatNo, CatName)

Data:
(2, 'bracket')
(3, 'cable')
etc...

with your 39 cats. Then you can get rid of all those tedious repeated SQL statements with just 2 SQL statements:

update ProductCategory
 set CategoryID = Cat.CatNo
 from Products
 join ProductCategory
 on ProductCategory.ProductID = Products.uid
 join Cat
 on Products.CatName = Cat.CatName
insert into ProductCategory
 (ProductID,
 CategoryID)
 select uid,
 Cat.CatNo
 from Products
 join Cat
 on Products.CatName = Cat.CatName
 left join ProductCategory
 on ProductCategory.ProductID = Products.uid
 where ProductCategory.ProductID is null

This 2 statements will do exactly the same as your 78.|||Looking at your question again I'm pretty sure your DB is not normalised. From your vague description, I believe your tables should be:

ProductCategory (ProductID, CategoryID)
Category (CategoryID, CategoryName) -- CategoryID should be auto-increment identity
Product (ProductID, CategoryID) --ProductID should be auto-increment identity

I also understand from your question and your existing stored proc that you link all categories into all products, in which case the ProdutCategory table becomes redundant unless it stores another column or two.