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

Answer 1

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

Answer 2

why not set a unique constraint on that column