-2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hloni Lydron
  • 1
  • 1
  • 1
  • 8
    You want `MERGE` . – Yogesh Sharma Oct 12 '18 at 13:05
  • 2
    To add to the above comment [`MERGE` (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017) – Thom A Oct 12 '18 at 13:08
  • 4
    Merge is great....except when it's not! I tend to avoid.... – Mitch Wheat Oct 12 '18 at 13:17
  • 3
    ^ see https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Aaron Bertrand Oct 12 '18 at 13:19
  • I've favorited this question just so I know where Aaron's article is! – Mitch Wheat Oct 12 '18 at 13:29
  • Possible duplicate of [Atomic UPSERT in SQL Server 2005](https://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005) – Ivan Starostin Oct 12 '18 at 13:55
  • Read Dan Guzman's [Conditional INSERT/UPDATE Race Condition](https://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx). @AaronBertrand what's your take about this article? – Zohar Peled Oct 14 '18 at 06:36
  • @Zohar that you can accomplish the same by wrapping the update/insert in an appropriate transaction. Adding the `IF EXISTS` doesn't gain you anything, except the need to add locking hints to make it safe. I don't think you're any further ahead here. I updated my answer. – Aaron Bertrand Oct 14 '18 at 14:15
  • @AaronBertrand thanks! Correct me if I'm wrong, but you are talking about a single row upsert here - what if you have a tvp where some rows already exists and some are new? – Zohar Peled Oct 14 '18 at 15:06
  • @Zohar For a TVP I would do `UPDATE ... JOIN` and `INSERT ... WHERE NOT EXISTS`. I would still avoid `MERGE`. – Aaron Bertrand Oct 14 '18 at 15:14
  • @AaronBertrand yes, I'm not suggesting using `merge`, especially after reading your article a few years back, but in this case wouldn't the `insert...select...where not exists` also need the hint locks Dan Guzman is writing about? – Zohar Peled Oct 14 '18 at 15:16
  • @Zohar I would still be wrapping both statements in a serializable transaction, so no, you wouldn't need locking hints on the individual statements. Putting things like holdlock on statements leads to, IMHO, plenty of elongated blocking and sometimes deadlocks because they are held for the length of the transaction. Using explicit tx controls gives you better visibility than statement hints (they can still be abused and lead to blocking/deadlocks, but the fix becomes more obvious). YMMV. – Aaron Bertrand Oct 14 '18 at 16:08

3 Answers3

11

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

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
UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
0

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)
John
  • 374
  • 2
  • 6
  • 1
    The `IF EXISTS` check is unnecessary and as a general pattern (not with #temp tables obviously) can actually contribute to concurrency issues / deadlocks. – Aaron Bertrand Oct 12 '18 at 13:25
  • I Agree with you on it causing an extra read, but on a temp table that looks like it only has 1 record it shouldn't be much of a performance hit. I generally use this still because it seems more readable and easier to follow to me than attempting an update and then checking rowcount. – John Oct 12 '18 at 15:46
  • 1
    The problem is people will learn from this and use this approach where it **does** matter because it could be a significant performance hit and it could cause concurrency issues or both. Also, comments [generally don't cause performance issues](https://sqlperformance.com/2016/11/sql-performance/comments-hamper-performance), so if you have a piece of code that you know is better but you find less readable or memorable how it works, add a comment. – Aaron Bertrand Oct 12 '18 at 15:51
  • 1
    (I already admitted it doesn't matter **here** but, because of how this site works, these answers will be read by plenty of future users with the same issue, but who may not be using a #temp table. Always try to answer questions with these future readers in mind.) – Aaron Bertrand Oct 12 '18 at 15:57