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
ENDSET @.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.CatNameinsert 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.
