/*
CREATE TABLE ApplicationsDesSocietes (
   id                   INT IDENTITY(0,1)    NOT NULL,
   applicationId        INT                  NOT NULL,
   societeId            INT                  NOT NULL,
   suppression          BIT                  NULL,
   CONSTRAINT PK_APPLICATIONSDESSOCIETES PRIMARY KEY (id)
)
GO
--*/

DECLARE @applicationId INT = 81, @societeId INT = 43, @suppression BIT = 0

MERGE dbo.ApplicationsDesSocietes WITH (HOLDLOCK) AS target
--set the SOURCE table one row
USING (VALUES (@applicationId, @societeId, @suppression))
    AS source (applicationId, societeId, suppression)
    --here goes the ON join condition
    ON target.applicationId = source.applicationId and target.societeId = source.societeId
WHEN MATCHED THEN
    UPDATE
    --place your list of SET here
    SET target.suppression = source.suppression
WHEN NOT MATCHED THEN
    --insert a new line with the SOURCE table one row
    INSERT (applicationId, societeId, suppression)
    VALUES (source.applicationId, source.societeId, source.suppression);
GO

用您需要的任何东西替换表和字段名。 注意使用状态。 然后在DECLARE行中为变量设置适当的值(和类型)。

欢呼。

您可以使用MERGE语句，此语句用于插入不存在的数据或更新存在的数据。

MERGE INTO Employee AS e
using EmployeeUpdate AS eu
ON e.EmployeeID = eu.EmployeeID`

做一个UPSERT:

UPDATE MyTable SET FieldA=@FieldA WHERE Key=@Key

IF @@ROWCOUNT = 0
   INSERT INTO MyTable (FieldA) VALUES (@FieldA)

http://en.wikipedia.org/wiki/Upsert

IF EXISTS (SELECT * FROM [Table] WHERE ID = rowID)
UPDATE [Table] SET propertyOne = propOne, property2 . . .
ELSE
INSERT INTO [Table] (propOne, propTwo . . .)

编辑:

唉，即使对我自己不利，我也必须承认，没有选择的解决方案似乎更好，因为它们少了一步就完成了任务。

不要忘记事务。性能很好，但是简单的方法(IF EXISTS..)非常危险。 当多个线程将尝试执行插入或更新时，您可以轻松地 获取主键冲突。

由@Beau Crawford和@Esteban提供的解决方案显示了大致的想法，但容易出错。

为了避免死锁和PK违规，你可以使用如下方法:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

or

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).

scproc通常在非常受控的条件下运行，并假设有可信的调用者(中间层)。这意味着如果一个简单的upsert模式(更新+插入或合并)看到重复的PK，这意味着在中间层或表设计中存在错误，SQL将在这种情况下调用错误并拒绝记录。在这种情况下，放置HOLDLOCK除了降低性能外，还等于吃掉异常并接收潜在的错误数据。

话虽如此，使用MERGE或UPDATE然后INSERT在您的服务器上更容易，而且更容易出错，因为您不必记得在第一次选择时添加(UPDLOCK)。此外，如果您正在小批量地进行插入/更新，则需要了解数据，以便决定某个事务是否合适。如果它只是一个不相关记录的集合，那么额外的“包络”交易将是有害的。