check value does not already exist in a column before inserting new record

Question

I have a table with two columns.

The first column is the primary key and SQL auto generates the value.

When a new record is inserted into my table, myTbl, I want to make sure that the ExternalId value does not currently exist in my table.

I was about to create an insert trigger but was thinking there might be a better / more efficient way than creating a trigger?

create table myTbl
(
    TeamId int not null identity(1000, 1),
    ExternalId nvarchar(14)
    constraint PK_myTbl primary key(TeamId)
)


     TeamId     ExternalId
     1000       DFJK
     1001       LMKG
     1002       PLKM

Possible duplicate of [Do conditional INSERT with SQL?](https://stackoverflow.com/questions/16636698/do-conditional-insert-with-sql) — shahkalpesh, Jul 24 '19 at 08:41

score 2 · Accepted Answer · answered Jul 24 '19 at 08:41

One simple option would be to just add unique constraint on the ExternalId column:

ALTER TABLE myTbl ADD CONSTRAINT cnstr_ext UNIQUE(ExternalId);

Then any attempt to insert a new record containing an ExternalId value which already is present in the table would result in an error.

If you are making this insert from an application language like Java or C#, you would get an exception in your code which you may handle as you want.

Matthew Baker · Answer 2 · 2019-07-24T09:17:08.883

If you are not performing a RBAR insert then there are a couple of options. Which works quickest will depend on the shape of your data. The two options below function by either testing to see if the value is already in the table, or merging the two tables together. I've included the merge as sometimes its usefull to be able to add or update rows in one command.

There is good documentation on the merge command widely available.

IF OBJECT_ID('tempdb..#myTbl') IS NOT NULL DROP TABLE #myTbl;

DECLARE @values_to_add_1 TABLE (ExternalId NVARCHAR(14));

DECLARE @values_to_add_2 TABLE (ExternalId NVARCHAR(14));

CREATE TABLE #myTbl (TeamId     INT NOT NULL IDENTITY(1000, 1)
,                    ExternalId NVARCHAR(14)
                         CONSTRAINT PK_myTbl
                         PRIMARY KEY (TeamId));

INSERT INTO #myTbl (ExternalId)
VALUES ('DFJK')
,      ('LMKG')
,      ('PLKM');

INSERT INTO @values_to_add_1
VALUES ('DFJK'), ('1234');

INSERT INTO @values_to_add_2
VALUES ('LMKG'), ('5678');

SET STATISTICS IO, TIME ON;

INSERT INTO #myTbl (ExternalId)
SELECT  ExternalId
  FROM  @values_to_add_1 VTA
 WHERE  NOT EXISTS (SELECT  1 FROM  #myTbl MT WHERE MT.ExternalId = VTA.ExternalId);

MERGE #myTbl Target
USING (SELECT   * FROM  @values_to_add_2) Source
   ON Target.ExternalId = Source.ExternalId
 WHEN NOT MATCHED BY TARGET THEN INSERT (ExternalId)
                                 VALUES (Source.ExternalId);

SET STATISTICS IO, TIME OFF;

SELECT * FROM #myTbl

If you are only adding a single row at a time, then depending on how many time you are expecting to hit duplicates the "it's better to ask forgiveness than permission" approach can be quicker. If your writing RBAR, and throwing an error doesn't stop your code then the constraint only has minor overhead.

GuidoG · Answer 3 · 2019-07-24T09:14:28.880

You have 2 options, either use an unique constraint on the table, or check with an sql statement every time before you insert.

Since the second option is much less efficient and has several problems, I suggest the first option.

alter table myTbl add constraint UX_ExternalId unique(ExternalId);

Now you can put the insert inside a try ... catch and you only need to take action when an error occurs

The second option has a few problems:

The check statement and the insert needs to be in the same transaction
The check statement has to be done every time, thus also when no duplicate, which is very inefficient
If anyone tries to insert with other software, there is no gurantee he will also do this check

Note that the second option only is logically sound if both the check and insert happen within a single serializable transaction. — Tim Biegeleisen, Jul 24 '19 at 08:48

check value does not already exist in a column before inserting new record

3 Answers3