Conditionally insert a row if it does not exist already

Question

[Note: I found a few answers for this, such as 9911659 and 16636698, but they were not quite clear enough on the syntax.]

I want to insert a row into a table, but only if that row does not already exist. The column values for the inserted row come from variables (procedure arguments) and not from another table, so I won't be using merge.

I do not want to use a separate if exists followed by an insert, but rather I'd like to accomplish this in a single (insert) statement.

I have @bookID, @userID, @reviewDate, and @reviewYear as arguments to my proc, which I want to insert into the new row into table my_table.

So I have this:

insert into my_table
    (bookID, reviewYear, userID, reviewDate)
select
    @bookID, @reviewYear, @userID, @reviewDate   -- Proc arguments, values for new row
from my_table
where not exists (
    select bookID                                -- Find existing matching row
    from my_table
    where bookID = @bookID
        and reviewYear = @reviewYear
)

In other words, the insert adds a new row only if there is not already an existing row with the same bookID and reviewYear. So a given user can add a review for a given book for a given year, but only if no user has already done so.

Have I got this correct, or is there a simpler syntax to accomplish the same thing (in a single statement)?

Addendum (2020-Jan-10)

As pointed out, the select will choose multiple rows, and the whole insert statement will end up inserting many rows, potentially as many rows as there are currently in my_table.

A second attempt, which adds a distinct clause to the select:

insert into my_table
    (bookID, reviewYear, userID, reviewDate)
select distinct                                  -- Only one possible match
    @bookID, @reviewYear, @userID, @reviewDate   -- Proc arguments, values for new row
from my_table
where not exists (
    select bookID                                -- Find existing matching row
    from my_table
    where bookID = @bookID
        and reviewYear = @reviewYear
)

That is a single statement? And yes it is correct. You can of course add a unique constraint to enforce that no duplicates are added. But would still write your insert as you have done. — Dale K, Nov 25 '19 at 20:36
I don't understand your goal here. What makes one syntax "simpler" than another, and why would you want it? Less typing? You've already done the typing, so what are you after and why? — Tab Alleman, Nov 25 '19 at 20:55
@TabAlleman - By 'simpler', I mean 'less complicated' and also 'less computational overhead'. For this specific problem, I'm asking if there is a simpler way to do the same thing but with a simpler subquery or without a subquery at all. — David R Tribble, Nov 25 '19 at 21:44

score 2 · Answer 1 · answered Nov 25 '19 at 20:57

I would recommend catching an error instead:

create unique index unq_my_table on my_table(bookID, reviewYear)

begin try
    insert into my_table (bookID, reviewYear, userID, reviewDate)
        values ( @bookID, @reviewYear, @userID, @reviewDate )  -- Proc arguments, values for new row

end try
begin catch
-- do something here if you want
end catch;

Your code does not work because you are selecting from the table. You will get as many inserts as in the table -- and you are likely to insert duplicates.

To prevent duplication, let the database ensure uniqueness. This is one of the things they can guarantee. And a unique index/constraint does this.

Laughing Vergil · Answer 2 · 2020-01-13T17:42:37.013

Edited:

Based on the clearer description above (and possibly on my coffee), I should note that you CAN use MERGE with just variables. Using your parameters above, here is one method of doing that:

WITH Src as (
    SELECT 
        @bookID AS bookID, 
        @reviewYear AS reviewYear, 
        @userID AS userID, 
        @reviewDate AS reviewDate
    )
MERGE my_table AS TARGET
USING Src AS SOURCE
ON TARGET.bookID = SOURCE.bookID
    AND TARGET.reviewYear = SOURCE.reviewYear
WHEN NOT MATCHED [BY TARGET]
    THEN INSERT (bookID, reviewYear, userID, reviewDate)
    VALUES (SOURCE.bookID, SOURCE.reviewYear, SOURCE.userID, SOURCE.reviewDate)

Original Answer:

Actually, I ran this code as posted, and it did not correctly enter the data into the table. Your basic problem here is your SELECT ... FROM my_table. This will attempt to insert as many rows into your table as the table contains. So, if the table is empty, no rows will be inserted, but if it has 20 rows, another 20 rows will be inserted.

Here is a correct method to do this. It uses your basic logic, but takes the conditional check out of the INSERT statement.

CREATE TABLE #my_table (BookID int, ReviewYear int, UserId int, ReviewDate date)

DECLARE @BookID int = 1,
    @ReviewYear int = 1999,
    @UserId Int = 111,
    @ReviewDate date = '2019-09-11'

IF NOT EXISTS (
    select \*                                -- Find existing matching row
    from #my_table
    where bookID = @bookID
        and reviewYear = @reviewYear
    )
insert into #my_table 
    (bookID, reviewYear, userID, reviewDate)
VALUES 
    (@bookID, @reviewYear, @userID, @reviewDate)   -- Proc arguments, values for new row

SELECT \*
FROM #my_table

That still executes separate `select` and `insert` statements, which is what I am trying to avoid. — David R Tribble, Jan 10 '20 at 22:47

Conditionally insert a row if it does not exist already

2 Answers2