Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.4k points)

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. 

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...