Insert multiple values and ignore duplicates on not key field

Question

If I want to insert multiple values to a table with fields:

ID | name | content  
---+------+------------------
 1 | john | some content  
 2 | bob  | another content

and now I want to insert multiple values:

INSERT INTO persons (name,content)
VALUES
("Foo","meh"),
("Moo","beh"),
("bob","huh"),
("dude","haha")

that the result should be:

ID | name | content  
---+------+------------------
 1 | john | some content  
 2 | bob  | another content
 3 | Foo  | meh
 4 | Moo  | beh
 5 | dude | haha

The query ignores duplicates by name so "bob"|"huh" is not inserted, the name is not a key field. I can't find anywhere the syntax for this, I know it should be something simple.

I'm using MySQL database.

Answer 1

Use LEFT JOIN to determine if record is already in table

SqlFiddleDemo

CREATE TABLE persons(ID INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100),
      content VARCHAR(100));

INSERT INTO persons (name, content)
VALUES ('john', 'some content'), ('bob', 'another content');

INSERT INTO persons (name,content)
SELECT t.name, t.content
FROM
(
  SELECT "Foo" AS name,"meh" AS content
  UNION ALL
  SELECT "Moo","beh"
  UNION ALL
  SELECT "bob","huh"
  UNION ALL
  SELECT "dude","haha"
) AS t
LEFT JOIN persons p
ON p.name = t.name
WHERE p.id IS NULL;

SELECT *
FROM persons;

Same using NOT EXISTS

INSERT INTO persons (name,content)
SELECT t.name, t.content
FROM
(
  SELECT "Foo" AS name,"meh" AS content
  UNION ALL
  SELECT "Moo","beh"
  UNION ALL
  SELECT "bob","huh"
  UNION ALL
  SELECT "dude","haha"
) AS t
WHERE NOT EXISTS 
     (SELECT 1 FROM persons p WHERE p.name = t.name)

EDIT:

Add UNIQUE KEY and use INSERT IGNORE INTO

SqlFiddleDemo

CREATE TABLE persons(ID INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) UNIQUE KEY,
      content VARCHAR(100));

INSERT INTO persons (name, content)
VALUES ('john', 'some content'), ('bob', 'another content');


INSERT IGNORE INTO persons (name,content)
VALUES
("Foo","meh"),
("Moo","beh"),
("bob","huh"),
("dude","haha");

SELECT *
FROM persons;

Answer 2

This is in tsql but you could create a stored procedure with a merge statement that has a table type (name and content as columns) as a parameter to check the name before inserting. If the name matches then it will not insert but if it doesn't match then it will insert a new record.

BEGIN TRANSACTION

-- setup tables for merge
MERGE dbo.persons AS t

USING @NameContentTable AS s
    ON t.name = s.name      

-- do nothing if matched

-- insert if not matched
WHEN NOT MATCHED THEN   
    INSERT (name, 
            content)
    VALUES (s.name,
            s.content);

-- error handling
IF @@ERROR <> 0 
BEGIN
    SELECT 'Unexpected error occurred'
    ROLLBACK TRAN 
    RETURN -1
END

COMMIT TRANSACTION