SQL Server Insert if not exist

Question

I want to insert data in my table, but insert only that doesn't exist in my DB!

here is my code:

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

And the error is:

Msg 156, Level 15, State 1, Procedure EmailsRecebidosInsert, Line 11

Incorrect syntax near the keyword 'WHERE'.

Answer 1

Instead of using the second part of your code i.e.

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

You can replace replace it with

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

Note: For high load, this can fail sometimes, because the second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition.