Sql insert multiple rows if not exists

Question

I have a sql table that has two columns id and name. I have list of names about 20 and I need to write a query that checks if name exists before insert.

Is there a better way of doing this rather then just having the below query 20 times but with different names (I need do this in t-sql):

IF NOT EXISTS(SELECT* 
              FROM   mytable 
              WHERE  name = 'Dan') 
  BEGIN 
      INSERT INTO mytable 
                  (name) 
      VALUES     ('dan') 
  END 

Answer 1

INSERT INTO MyTable (Name)
SELECT  NewNames.Name
FROM    ( VALUES ('Name1'), ('Name2'), ('Name3') ) AS NewNames (Name)
WHERE   NOT EXISTS ( SELECT 1
                     FROM   MyTable AS MT
                     WHERE  MT.Name = NewNames.Name );

Answer 2

I think you could use a merge statement:

MERGE INTO myTable AS Target
USING (VALUES ('name1'),('name2'),('...')) AS source (NAME)
ON Target.NAME = Source.NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT (NAME) VALUES (name)

Answer 3

You can filter values with NOT EXISTS

INSERT INTO myTable (
    Name
)
SELECT DISTINCT
    Name
FROM (
        VALUES ('Name 1'),
               ('Name 2')
    ) AS NewNames(Name)
WHERE
    NOT EXISTS (SELECT 1 FROM TargetTable WHERE myTable.Name = NewNames.Name)

If your new names are in another table, you can change the select query in the above one.

Please note, that the DISTINCT keyword is necessary to filter out the duplications in the source data.

Answer 4

I would do this using insert:

with names as (
      select 'Dan' as name union all
      select 'name2' union all
      . . .
     )
insert into myTable(name)
    select distinct name
    from myTable
    where not exists (select 1 from mytable t2 where t2.name = t.name);

Note: you may want to create a unique index on mytable(name) so the database does the checking for duplicates.

Answer 5

INSERT INTO MyTable (Name)
SELECT Name FROM
(
    VALUES ('Name 1'),
           ('Name 2')
) AS Names(Name)
WHERE Name NOT IN
(
    SELECT Name FROM MyTable
)

Answer 6

untested so there might be some minor errors:

merge into mytable x
using (
    values ('name1')
         , ('name2')
         , ...
         , ('namen')
) as y (name)
    on x.name = y.name
when not matched then 
    insert (name)
    values (y.name)

Answer 7

INSERT IGNORE INTO myTable (column1, column2) VALUES (val1, val2),(val3,val4),(val5,val6);

INSERT IGNORE will allow skip on duplicate values