insert unique values only

Question

I need to find the most efficient way to insert a value into table but only when that value is not already in the table.

here is a sample table:

DECLARE @Table table (TheValue int primary key)

sample data to insert:

INSERT @Table SELECT 1 UNION SELECT 2 UNION SELECT 3 --existing data
DECLARE @x int;set @x=5                              --data to add

here are all the ideas I can think of to do this. Which one is best and/or is there a better way? This is a batch process, so there is no risk of another process inserting data, so no locking is necessary in Try 1.

Try 1:

IF NOT EXISTS (SELECT 1 FROM @Table WHERE TheValue=@x)
BEGIN
    INSERT @Table VALUES (@x)
END

Try 2:

INSERT @Table SELECT @x EXCEPT SELECT TheValue FROM @Table

Try 3:

INSERT @Table SELECT @x WHERE @X NOT IN (SELECT TheValue FROM @Table)

Try 4:

BEGIN TRY
    INSERT @Table VALUES (@x)
END TRY
BEGIN CATCH END CATCH

You can take a look at this link http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ by @Quassnoi and see the difference in performance of `NOT IN`, `NOT EXISTS` and `LEFT JOIN`. — Lamak, Apr 29 '11 at 19:25
There is no scenario which will always be fastest. I would expect #2 to be most efficient in most cases, but you'll have to profile to be sure. — , Apr 29 '11 at 19:29
possible duplicate of [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Apr 29 '11 at 19:32
This has been asked dozens of times already...please utilize the search function before asking a new question! — JNK, Apr 29 '11 at 20:22
`unique insert sql` to start...second hit should answer your question. — JNK, Apr 29 '11 at 20:32

score 2 · Answer 1 · answered Apr 29 '11 at 19:21

2

why not set a unique constraint on that column

answered Apr 29 '11 at 19:21

Sam Axe

33,313
9
55
89

the `primary key` part in `DECLARE @Table table (TheValue int primary key)` sets a unique constraint on the column. – RacerX Apr 29 '11 at 19:30
then I must be missing something. just run the update statement and if the value already exists mssql will throw an error, otherwise it will succeed. – Sam Axe Apr 29 '11 at 19:33
the `primary key` does provide a unique constraint. if you use the sample code from the question, and try to insert a duplicate, you'll get a `Msg 2627, Level 14, State 1, Line xyz Violation of PRIMARY KEY constraint...` message. – RacerX Apr 29 '11 at 19:41
Which seems to be in line with what you are attempting to do.. prevent duplicate values in that column.. What am I missing? – Sam Axe Apr 29 '11 at 19:42
AKA the JFDI pattern in the possible duplicate I linked to. – Martin Smith Apr 29 '11 at 19:57
@Boo, you're missing that I need tp preventing a duplicate in a nice way and not by terminating with an error. – RacerX Apr 29 '11 at 21:18
@Boo, see the question section `Try 4:` – RacerX May 02 '11 at 12:54

score 1 · Accepted Answer · answered Apr 29 '11 at 19:26

Here's one other way by self joining the table that your inserting to and only inserting records where it doesn't already exist.

First is the table that your doing your batch inserts to, where you want to maintain unique records. Keep in mind that you should have a unique constraint on here. You want to use one of these unique inserts only so that you never run into the constraint:

DECLARE @Table table (TheValue int primary key)

One of the tables that your getting your data from that you want to insert into the main batch table:

DECLARE @TableSelectingFrom table (TheValue int primary key)

For example, just populating this with a record:

insert @TableSelectingFrom select 1

There's a left outer join so that we only pull unique records from @TableSelectingFrom:

INSERT into @Table 
SELECT a.TheValue
from @TableSelectingFrom a
left join @Table b on a.TheValue = b.TheValue
where b.TheValue is null

select * from @Table

insert unique values only

2 Answers2