sqlcmd.net validated sql reference

Command guide

intermediate inserting MySQL MariaDB SQL Server PostgreSQL SQLite

Insert Rows and Silently Skip Duplicates

Insert rows without raising an error when a unique constraint is violated, using each engine's conflict-ignore syntax.

Created Last updated 5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Add new subscribers but skip rows whose email already exists

The incoming batch contains id 3 (alice@example.com) and id 4 (carol@example.com). Alice's email already exists in the table, so that row is silently dropped. Carol's email is new, so id 4 is inserted. The final table has three rows: the original two plus Carol. The original Alice row (id 1) is unchanged — this is an ignore, not an update.

MySQL MariaDB
Engine-specific syntax
Source table data
Setup
CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@example.com');
Validated query
SQL
INSERT IGNORE INTO subscribers (id, email)
VALUES
  (3, 'alice@example.com'),
  (4, 'carol@example.com');

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;
Expected result
idemail
1alice@example.com
2bob@example.com
4carol@example.com
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@example.com');
Validated query
SQL
INSERT INTO
  subscribers (id, email)
SELECT
  v.id,
  v.email
FROM
  (
    VALUES
      (3, 'alice@example.com'),
      (4, 'carol@example.com')
  ) AS v (id, email)
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      subscribers s
    WHERE
      s.email = v.email
  );

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;
Expected result
idemail
1alice@example.com
2bob@example.com
4carol@example.com
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@example.com');
Validated query
SQL
INSERT INTO
  subscribers (id, email)
VALUES
  (3, 'alice@example.com'),
  (4, 'carol@example.com') ON CONFLICT (email) DO NOTHING;

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;
Expected result
idemail
1alice@example.com
2bob@example.com
4carol@example.com
SQLite
Engine-specific syntax
Source table data
Setup
CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@example.com');
Validated query
SQL
INSERT
OR IGNORE INTO subscribers (id, email)
VALUES
  (3, 'alice@example.com'),
  (4, 'carol@example.com');

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;
Expected result
idemail
1alice@example.com
2bob@example.com
4carol@example.com

MySQL and MariaDB use `INSERT IGNORE INTO`. PostgreSQL uses `ON CONFLICT (email) DO NOTHING`. SQLite uses `INSERT OR IGNORE INTO`. SQL Server uses a `WHERE NOT EXISTS` subquery to pre-filter conflicting rows. All produce identical results.

Useful when

Where this command helps.

  • loading a batch of rows where some may already exist and duplicates should be silently dropped
  • importing data from an external feed without needing to pre-deduplicate it

Explanation

What the command is doing.

Each engine offers a way to insert rows and quietly discard any that would violate a UNIQUE or primary-key constraint, without aborting the entire statement. MySQL and MariaDB use INSERT IGNORE INTO, which silences constraint errors for affected rows. PostgreSQL uses ON CONFLICT DO NOTHING. SQLite uses INSERT OR IGNORE INTO. SQL Server has no direct keyword but achieves the same result by filtering the source with WHERE NOT EXISTS. Unlike an upsert, none of these forms update the existing row — they simply drop the conflicting input row.

Tags

insertuniqueconflictdml

Related commands

Keep moving through nearby tasks.

Official documentation