SQL: Synching Local Stock with Website Stock
---------------------------------------------------------------------------
-- SET @StockLocationID: Set this to the Stock Location you wish to Update
---------------------------------------------------------------------------
DECLARE @StockLocationID INT
SET @StockLocationID = 1
---------------------------------------------------------------------------
-- UPDATE Current Local Stock Values
---------------------------------------------------------------------------
UPDATE
StockLocationLevels
SET
StockLocationLevels.StockLevel = Stock.InStock
FROM
StockLocationLevels
INNER JOIN Stock
ON Stock.StockID = StockLocationLevels.StockID
WHERE
StockLocationLevels.StockLocationID = @StockLocationID
---------------------------------------------------------------------------
-- INSERT non-existant Local Stock Values
---------------------------------------------------------------------------
INSERT INTO StockLocationLevels
(
StockID,
StockLocationID,
StockLevel,
LocalPrice
)
SELECT
StockID,
@StockLocationID,
InStock,
Price
FROM
Stock
WHERE
Stock.StockID NOT IN (
SELECT
StockID
FROM
StockLocationLevels
WHERE
StockLocationID = @StockLocationID
)