CODEWITHSUNDEEP
sqlsql-serverinsert
user3884462
user3884462

Reputation: 201

Sql insert multiple rows if not exists

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

Upvotes: 18

Views: 39173

Answers (7)

Ralf
Ralf

Reputation: 1108

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 );

Upvotes: 27

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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.

Upvotes: 3

Jacek Pietal
Jacek Pietal

Reputation: 2019

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

INSERT IGNORE will allow skip on duplicate values

Upvotes: 0

Pred
Pred

Reputation: 9042

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.

Upvotes: 5

Radu Porumb
Radu Porumb

Reputation: 785

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

Upvotes: 2

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

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)

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6781

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)

Upvotes: 9

Related Questions