SQL Server 如果不存在则插入

问题描述：

我想向我的表中插入数据，但只插入我的数据库中尚不存在的数据.

I want to insert data into my table, but insert only data that doesn't already exist in my database.

这是我的代码:

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

错误是:

消息 156，级别 15，状态 1，过程 EmailsRecebidosInsert，第 11 行
关键字WHERE"附近的语法不正确.

Msg 156, Level 15, State 1, Procedure EmailsRecebidosInsert, Line 11
Incorrect syntax near the keyword 'WHERE'.

答

代替下面的代码

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 的指点)

Updated : (thanks to @Marc Durdin for pointing)

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

Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a transaction doesn't solve this.

SQL Server 如果不存在则插入

相关推荐