在插入 SQL Server 中使用 if 条件 [英] Using a if condition in an insert SQL Server

问题描述

我的代码中有以下语句

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

我想做类似的事情:

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

有没有办法做到这一点?

Is there a way I can do this?

推荐答案

模式是(没有错误处理):

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;

您无需在此处对#temp 表执行额外读取.您已经通过尝试更新来做到这一点.为了防止竞争条件，您可以像保护要隔离的两个或多个语句的任何块一样执行相同的操作:您将它包装在具有适当隔离级别的事务中(可能在此处可序列化，尽管这只是当我们谈论的不是 #temp 表时，这是有道理的，因为根据定义，这是序列化的).

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).

添加 IF EXISTS 检查(和 您需要添加锁定提示以确保安全/可序列化)，但您可能会落后，具体取决于您的次数更新现有行与插入新行.这可能会增加很多额外的 I/O.

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.

人们可能会告诉你使用MERGE(这实际上是幕后的多重操作，而也需要使用可序列化保护)，我劝你不要这样做.我在这里列出了原因:

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 lay out why here:

• 谨慎使用 SQL Server 的 MERGE声明

对于多行模式(如 TVP)，我会以完全相同的方式处理此问题，但没有一种实用的方法可以像处理单行情况那样避免第二次读取.不，MERGE 也不会避免它.

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.

这篇关于在插入 SQL Server 中使用 if 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！