The pattern is (without error handling):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE #TProductSales SET StockQty = @StockQty, ETA1 = @ETA1
WHERE ProductID = @ProductID;
IF @@ROWCOUNT = 0
BEGIN
INSERT #TProductSales(ProductID, StockQTY, ETA1)
VALUES(@ProductID, @StockQTY, @ETA1);
END
COMMIT TRANSACTION;
You don’t need to perform an additional read of the #temp table here. You’re already doing that by trying the update. To protect from race conditions, you do the same as you’d protect any block of two or more statements that you want to isolate: you’d wrap it in a transaction with an appropriate isolation level (likely serializable here, though that all only makes sense when we’re not talking about a #temp table, since that is by definition serialized).
You’re not any further ahead by adding an IF EXISTS check (and you would need to add locking hints to make that safe / serializable anyway), but you could be further behind, depending on how many times you update existing rows vs. insert new. That could add up to a lot of extra I/O.
People will probably tell you to use MERGE (which is actually multiple operations behind the scenes, and also needs to be protected with serializable), I urge you not to. I and others lay out why here:
For a multi-row pattern (like a TVP), I would handle this quite the same way, but there isn’t a practical way to avoid the second read like you can with the single-row case. And no, MERGE doesn’t avoid it either.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE t SET t.col = tvp.col
FROM dbo.TargetTable AS t
INNER JOIN @TVP AS tvp
ON t.ProductID = tvp.ProductID;
INSERT dbo.TargetTable(ProductID, othercols)
SELECT ProductID, othercols
FROM @TVP AS tvp
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.TargetTable
WHERE ProductID = tvp.ProductID
);
COMMIT TRANSACTION;
Well, I guess there is a way to do it, but I haven’t tested this thoroughly:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
DECLARE @exist TABLE(ProductID int PRIMARY KEY);
UPDATE t SET t.col = tvp.col
OUTPUT deleted.ProductID INTO @exist
FROM dbo.TargetTable AS t
INNER JOIN @tvp AS tvp
ON t.ProductID = tvp.ProductID;
INSERT dbo.TargetTable(ProductID, othercols)
SELECT ProductID, othercols
FROM @tvp AS t
WHERE NOT EXISTS
(
SELECT 1 FROM @exist
WHERE ProductID = t.ProductID
);
COMMIT TRANSACTION;
In either case, you perform the update first, otherwise you’ll update all the rows you just inserted, which would be wasteful.