CODEWITHSUNDEEP
sqlsql-serversql-server-2012
flip66
flip66

Reputation: 341

Can I INSERT only when a record doesn't exist in a single statement?

I have to insert values into a table with these columns: Id MasterId ZipCode Locality Value, CreatedTime, UpdatedTime

A unique value is defined by these 3 columns: MasterId ZipCode Locality

There is no unique constraint on the database across these columns, but I need to make sure that 2 users can't enter the same data. I am unable to make changes to the T-SQL Database schema.

Current SQL:

INSERT INTO dbo.Carrier VALUES (?, ?, ?, ?, ?, GETDATE(), GETDATE())

My SQL changes:

IF NOT EXISTS
(
    SELECT TOP 1 *
    FROM dbo.Carrier
    WHERE MasterId = ?
    AND ZipCode = ?
    AND Locality = ?
)
BEGIN
    INSERT INTO dbo.Carrier VALUES (?, ?, ?, ?, ?, GETDATE(), GETDATE())
END

These changes work for standard user input, but there is also a service that inserts many entries at the same time. Sometimes this service will try to insert thousands of the same unique rows in a multi-threaded fashion and the SQL has a race condition, because it is not one statement. This allows duplicates to be entered when 2 statements are executing simultaneously.

Is there a way to insert only when a record doesn't exist in a single statement?

I don't have this problem on UPDATE, because I can do a where UpdatedTime hasn't changed.

Upvotes: 0

Views: 576

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would do this as a single statement using not exists:

insert into dbo.Carrier (masterid, zipcode, locality, . . . )
    select v.*
    from (values (?, ?, ?, ... ), (?, ?, ?, ...)) v(masterid, zipcode, locality, . . .)
    where not exists (select 1
                      from dbo.Carrier c
                      where c.masterid = v.masterid and c.zipcode = v.zipcode and c.locality = v.locality
                     );

EDIT:

If you want to protect the database for race conditions, you should have the database do the protection. Add a unique index/constraint on (masterid, zipcode, locality). Then you should just ignore any error that violates the constraint.

Upvotes: 6

Void Ray
Void Ray

Reputation: 10199

Using a CTE and a Join:

;with new_record as (
    select 1 as MasterId , 90065 as ZipCode, 1 as Locality
)
insert into Carrier(MasterId, ZipCode, Locality)
select a.MasterId, a.ZipCode, a.Locality
from new_record a
left join Carrier b
on a.MasterId = b.MasterId
    and a.ZipCode = b.ZipCode
    and a.Locality = b.Locality
where b.MasterId is null

Upvotes: 0

kicken
kicken

Reputation: 2167

A MERGE statement solution would be something like this:

MERGE INTO Carrier dst
USING (VALUES(?,?,?,...)) as src(MasterId, Zipcode, Locality,...)
ON src.MasterId=dst.MasterId AND src.Zipcode=dst.Zipcode AND src.Locality=dst.Locality
WHEN NOT MATCHED THEN INSERT (
    MasterId, Zipcode, Locality
    , ...
) VALUES (
    src.MasterId, src.Zipcode, src.Locality
    , ...
)

If desired you could also have the merge statement do an UPDATE on matching rows at the same time as it inserts missing rows.

Upvotes: 1

Related Questions