SQL Server 如果不存在则插入

Question

我想向我的表中插入数据，但只插入数据库中不存在的数据。 这是我的代码：

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

错误信息如下： Msg 156，级别 15，状态 1，过程 EmailsRecebidosInsert，行 11 关键字 'WHERE' 附近的语法不正确。

- Francisco Carvalho

---

仅靠这个检查就不能确保没有重复数据，因为它不具备线程安全性，当出现竞争条件时会出现重复数据。如果你确实需要唯一的数据，请在表中添加唯一约束，并捕获唯一约束违规错误。[请参见此答案]（https://dev59.com/Z3A75IYBdhLWcg3wOWVd#3408196）。 - GarethD

你可以使用MERGE查询或者IF NOT EXISTS（SELECT语句） 开始 插入值 结束 - Abdul Hannan Ijaz

@GarethD：你说的“不支持线程安全”是什么意思？虽然它看起来不太优雅，但在我看来它是正确的。单个insert语句始终是单个事务。SQL Server不会先评估子查询，然后在某个稍后的时间点，而且没有保持锁定的情况下进行插入。 - Ed Avis

@EdAvis 这正是发生的情况，除非您明确使用事务和 UPDLOCK 和 HOLDLOCK 查询提示，否则在检查完成后，对 EmailsRecebidos 的锁定将立即释放，就在写入同一表之前的瞬间。在这一刹那间，另一个线程仍然可以读取表并假设记录不存在，并遇到竞争条件。通过使用显式事务和锁定提示，可以阻止在选择语句完成后释放表上的锁定。锁将保持到事务提交为止。 - GarethD

这里进一步讨论了条件INSERT/UPDATE竞争条件。然而，如果数据应该是唯一的，那么它应该被限制为唯一的。没有约束条件，就没有阻止某人完全绕过存储过程，并使用简单的插入脚本插入重复记录的方法。 - GarethD

Answer 1

使用以下代码替代原有代码

BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

替换为

BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

更新: (感谢@Marc Durdin指出)

请注意，在高负载下，这仍然有时会失败，因为第二个连接可以在第一个连接执行INSERT之前通过IF NOT EXISTS测试，即竞态条件。有关为什么即使在事务中包装也无法解决此问题的好答案，请参见stackoverflow.com/a/3791506/1836776。

- Imran Ali Khan

---

请注意，高负载情况下这仍然有时会失败，因为第二个连接可能会在第一个连接执行插入之前通过IF NOT EXISTS测试，即竞争条件。有关为什么即使在事务中包装也无法解决此问题的良好答案，请参见https://dev59.com/O2865IYBdhLWcg3wfeqU#3791506。 - Marc Durdin

使用"SELECT 1"而不是"*"会更高效。原文的意思为：在查询"EmailsRecebidos"表时，通过指定条件"De = @_DE"、"Assunto = @_ASSUNTO"和"Data = @_DATA"来选取满足条件的行。 - Reno

在整个过程中放置一个写锁，这样就不会有重复的机会了。 - Kevin Finkenbinder

在这种情况下，“select *”毫无区别，因为它被用在“EXISTS”子句中。 SQL Server将始终进行优化，并且已经使用了很长时间。由于我很老，通常会写成“EXISTS（SELECT 1 FROM ...）”的查询，但现在不再需要了。 - Loudenvier

为什么这种简单的问题会产生更多的怀疑而不是确定性？ - drowa

Answer 2

对于那些寻求最快速方式的人，我最近发现了这些基准测试，显然使用“INSERT SELECT... EXCEPT SELECT...”在处理5000万条记录或更多时是最快的。

以下是文章中的一些示例代码（第3个代码块最快）：

INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
-----------------------------------
MERGE #table1 as [Target]
USING  (select Id, guidd, TimeAdded, ExtraData from #table2) as [Source]
(id, guidd, TimeAdded, ExtraData)
    on [Target].id =[Source].id
WHEN NOT MATCHED THEN
    INSERT (id, guidd, TimeAdded, ExtraData)
    VALUES ([Source].id, [Source].guidd, [Source].TimeAdded, [Source].ExtraData);
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT id, guidd, TimeAdded, ExtraData from #table2
EXCEPT
SELECT id, guidd, TimeAdded, ExtraData from #table1
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT #table2.id, #table2.guidd, #table2.TimeAdded, #table2.ExtraData
FROM #table2
LEFT JOIN #table1 on #table1.id = #table2.id
WHERE #table1.id is null

- user4023224

---

我喜欢除了选择以外的所有东西。 - Bryan

第一次使用EXCEPT。简单而优雅。 - jhowe

@Biswa：根据那些基准测试并不是这样的。代码可以从网站上获取。请随意在您的系统上运行它，以查看结果如何比较。 - user4023224

注意，这些基准测试是针对“从另一个表插入”而不是问题中要求的显式值进行测试的。 - Alex from Jitbit

一直在尝试这些代码；看起来 EXCEPT 表单的缺点是只有当现有记录中的每个字段都与要插入的记录匹配时，它才会跳过插入。这可能没问题。但对我来说，我只想跳过 ID 存在的情况，而不管其他值如何，所以我选择了第一种形式。虽然从未使用过 EXCEPT，但非常酷。 - Jason C

Answer 3

不同的SQL，但原则相同。只有当where not exists子句失败时才进行插入

INSERT INTO FX_USDJPY
            (PriceDate, 
            PriceOpen, 
            PriceLow, 
            PriceHigh, 
            PriceClose, 
            TradingVolume, 
            TimeFrame)
    SELECT '2014-12-26 22:00',
           120.369000000000,
           118.864000000000,
           120.742000000000,
           120.494000000000,
           86513,
           'W'
    WHERE NOT EXISTS
        (SELECT 1
         FROM FX_USDJPY
         WHERE PriceDate = '2014-12-26 22:00'
           AND TimeFrame = 'W')

- Malcolm Swaine

---

非常优雅的解决方案，这也应该解决竞态条件问题。 - Fabio Pagano

@FabioPagano 怎么做？ - Andrew

因为它在查询本身中而不是在过程之前，所以这个值不可能被重复，因为插入期间会检查其存在性。 - Rob

我不知道性能如何，但那个运行良好。 - ojonasplima

Answer 4

我会使用合并操作：

我会使用合并操作：

create PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   with data as (select @_DE as de, @_ASSUNTO as assunto, @_DATA as data)
   merge EmailsRecebidos t
   using data s
      on s.de = t.de
     and s.assunte = t.assunto
     and s.data = t.data
    when not matched by target
    then insert (de, assunto, data) values (s.de, s.assunto, s.data);
END

- Brett Schneider

---

我选择这个是因为它更加高端。 - jokab

我很想使用合并（merge）操作，但它不适用于内存优化表。 - Don Sam

Merge还允许您在需要时更新已存在的行，例如，如果您要用新价格更新杂货清单 - undefined

Answer 5

请尝试以下代码

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   select @_DE, @_ASSUNTO, @_DATA
   EXCEPT
   SELECT De, Assunto, Data from EmailsRecebidos
END

- SaravanaC

---

请提供一个好的解释，说明你的代码为什么有效！ - Abdelghani AINOUSS

Answer 6

我在 SQL Server 2012 上做了同样的事情，它也起作用了。

Insert into #table1 With (ROWLOCK) (Id, studentId, name)
SELECT '18769', '2', 'Alex'
WHERE not exists (select * from #table1 where Id = '18769' and studentId = '2')

- Hovhannes Babayan

---

当然成功了，因为你正在使用临时表（即在使用临时表时无需担心并发性问题）。 - drowa

我收到了一个“位置不在预期位置”的错误。 - Blaisem

你正在写哪种编码，使用哪个SQL服务器？ - Hovhannes Babayan

Answer 7

INSERT 命令没有 WHERE 子句 - 您需要这样编写：

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

- marc_s

---

由于在检查和插入之间可能会出现插入情况，因此需要处理这个过程中的错误。 - Filip De Vos

@FilipDeVos: true - 一种可能性，也许不太可能，但仍然是一种可能性。好观点。 - marc_s

如果你将它们都包含在一个事务中，那么会阻止这种可能性吗？（我对事务不是很了解，所以请原谅如果这是一个愚蠢的问题。） - David

参见https://dev59.com/O2865IYBdhLWcg3wfeqU#3791506，这里有一个很好的答案解释为什么事务不能解决这个问题，@David。 - Marc Durdin

在IF语句中：如果所需的命令行数只有一个，即使您使用了多行，也无需使用BEGIN和END，因此可以在此处省略它。 - Wessam El Mahdy

Answer 8

只需将您的代码更改为使用SELECT而不是VALUES

   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   SELECT @_DE, @_ASSUNTO, @_DATA
   WHERE NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);

- Leon Tayson

---

仅返回翻译文本：只有正确答案，令人难以置信 - gordy

对我来说，它看起来像是两个独立的语句：一个是无论数据库当前状态如何都进行插入操作，另一个是基于某个子查询选择3列。自然而然，我认为我可能弄错了，这就引出了一个问题，我漏掉了什么。请给予指导。 - Konrad Viltersten

@KonradViltersten 这是一个单一的语句。INSERT INTO 不能没有任何值而正常工作（你可以试一下，会出现语法错误），所以接下来的内容将被解释为要插入的值。这可以是一个带有静态值的 VALUES 子句，或者像这种情况下，是一个完整功能的 SELECT 的结果集。 - BenderBoy

@BenderBoy 哇！我刚刚看到了。我知道我漏掉了什么！你能想象我做后端工作已经有17年左右，却从来没有用过这个？我总是使用values(...)并放入基于之前查询声明的变量。这真是一个顿悟。该死...谢谢，伙计！ - Konrad Viltersten

Answer 9

如果您的聚集索引仅由这些字段组成，那么简单、快速和可靠的选项是使用IGNORE_DUP_KEY

如果使用 IGNORE_DUP_KEY ON 创建聚集索引

然后您可以只需使用：

INSERT INTO EmailsRecebidos (De, Assunto, Data) VALUES (@_DE, @_ASSUNTO, @_DATA)

这在所有情况下都应该是安全的！

- Alexander Bartosh

---

我不知道这个，非常感谢，这是一个非常巧妙的解决方案！ - Christoph

Answer 10

根据您的SQL Server版本（2012？），除了使用IF EXISTS之外，您还可以像这样使用MERGE：

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
    ( @_DE nvarchar(50)
    , @_ASSUNTO nvarchar(50)
    , @_DATA nvarchar(30))
AS BEGIN
    MERGE [dbo].[EmailsRecebidos] [Target]
    USING (VALUES (@_DE, @_ASSUNTO, @_DATA)) [Source]([De], [Assunto], [Data])
         ON [Target].[De] = [Source].[De] AND [Target].[Assunto] = [Source].[Assunto] AND [Target].[Data] = [Source].[Data]
     WHEN NOT MATCHED THEN
        INSERT ([De], [Assunto], [Data])
        VALUES ([Source].[De], [Source].[Assunto], [Source].[Data]);
END

- Don