Using a if condition in an insert SQL Server

Question

I have the following statement in my code

INSERT INTO #TProductSales (ProductID, StockQTY, ETA1) 
VALUES (@ProductID, @StockQTY, @ETA1)

I want to do something like:

IF @ProductID exists THEN 
   UPDATE #TProductSales 
ELSE 
   INSERT INTO #TProductSales

Is there a way I can do this?

Answer 1

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:

• Use Caution with SQL Server's MERGE Statement
• So, you want to use MERGE, eh?

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.

Answer 2

I personally like to make a table variable or temp table to store the values and then do my update/insert, but I'm normally doing mass insert/updates. That is the nice thing about this pattern is that it works for multiple records without redundancy in the inserts/updates.

DECLARE @Tbl TABLE (
    StockQty INT,
    ETA1 DATETIME,
    ProductID INT
)

INSERT INTO @Tbl (StockQty,ETA1,ProductID)
    SELECT @StockQty AS StockQty ,@ETA1 AS ETA1,@ProductID AS ProductID

UPDATE tps
SET   StockQty = tmp.StockQty
    , tmp.ETA1 = tmp.ETA1
FROM #TProductSales tps
INNER JOIN @Tbl tmp ON tmp.ProductID=tps.ProductID

INSERT INTO #TProductSales(StockQty,ETA1,ProductID)
    SELECT
        tmp.StockQty,tmp.ETA1,tmp.ProductID
    FROM @Tbl tmp
    LEFT JOIN #TProductSales tps ON tps.ProductID=tmp.ProductID
    WHERE tps.ProductID IS NULL

Answer 3

You could use something like:

IF EXISTS( SELECT NULL FROM  #TProductSales WHERE ProductID = @ProductID)
     UPDATE #TProductSales SET StockQTY = @StockQTY, ETA1 = @ETA1 WHERE ProductID = @ProductID
ELSE
     INSERT INTO #TProductSales(ProductID,StockQTY,ETA1) VALUES(@ProductID,@StockQTY,@ETA1)