ALTER PROCEDURE [dbo].[spPassPurchaseDataToProductStock]
AS
BEGIN
SELECT MAX(ItemName) ItemName, ItemCode, SUM(Quantity) Quantity
INTO #tmp
FROM tblPurchase
GROUP BY itemCode;
UPDATE tblProductStock
SET Quantity = #tmp.Quantity
FROM #tmp
WHERE ItemCode = #tmp.ItemCode
INSERT INTO tblProductStock (ItemName, ItemCode, Quantity)
SELECT ItemName, ItemCode, Quantity
FROM #tmp
WHERE ItemCode NOT IN (SELECT ItemCode FROM tblProductStock)
DROP TABLE #tmp;
END
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…