SQL Server Insert if not exists

How to Insert Data into SQL Server Table Only if it Doesn't Exist 🗄️

Are you tired of duplicating data in your SQL Server table? Do you want to ensure that only unique data gets inserted? 🤔 Well, you're in luck! In this blog post, we'll explore a common issue faced by SQL developers: how to insert data into a table only if it doesn't already exist.

The Problem 👾

Let's say we have a EmailsRecebidos table that stores received emails. We want to insert new email information into this table, but only if it doesn't already exist. If a duplicate email is inserted, it should be ignored to prevent data redundancy.

The Frustrating Code 😩

Here is the code that we have initially tried:

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

The Confusing Error 🚫

But running this code triggers the following error message:

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

The Solution 💡

The reason for the error is that the WHERE clause cannot be used directly with the INSERT INTO statement. To overcome this limitation, we can use the MERGE statement to achieve the desired functionality.

Here's an updated version of the code using the MERGE statement:

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   MERGE EmailsRecebidos AS target
   USING (SELECT @_DE AS De, @_ASSUNTO AS Assunto, @_DATA AS Data) AS source
   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

Now, when executing the stored procedure, this code will insert a new row into the table only if the combination of De, Assunto, and Data values doesn't already exist in the EmailsRecebidos table.

The Call-to-Action 📣

You're now armed with the knowledge to insert data into a SQL Server table only if it doesn't exist. Give it a try and let us know how it worked for you! If you have any questions or encountered any issues, feel free to leave a comment below. Happy coding! 💻🚀