Oracle SQL：向带唯一约束的新建表插入数据并避免重复

Hey there! Let's work through that unique constraint error you're running into when populating Table2. Since Table2 is brand new, and you only want to bring over entries 1, 2, and 4 from Table1 (while skipping any duplicates), here are tailored solutions based on common database systems:

Core Approach: Filter & Insert, Skip Conflicts #

First, we'll target only the rows you care about from Table1, then use database-specific syntax to ignore any rows that would violate the Name unique constraint.

For MySQL/MariaDB #

Use INSERT IGNORE—it automatically skips rows that trigger constraint errors without throwing an exception:

INSERT IGNORE INTO Table2 (ID, Name)
SELECT ID, Name
FROM Table1
WHERE ID IN (1, 2, 4);

This will insert all valid rows from your target ID list, and quietly skip any duplicates in the Name column.

For PostgreSQL #

PostgreSQL uses ON CONFLICT DO NOTHING for granular control over conflict handling:

INSERT INTO Table2 (ID, Name)
SELECT ID, Name
FROM Table1
WHERE ID IN (1, 2, 4)
ON CONFLICT (Name) DO NOTHING;

Here, we explicitly tell the database to do nothing when a duplicate Name is encountered, so only non-conflicting rows get inserted.

For SQL Server #

You have two solid options here:

Option 1: MERGE Statement

This lets you match existing rows and only insert new ones:

MERGE INTO Table2 AS target
USING (
    SELECT ID, Name FROM Table1 WHERE ID IN (1, 2, 4)
) AS source
ON target.Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (ID, Name) VALUES (source.ID, source.Name);

Option 2: INSERT + NOT EXISTS

A simpler alternative that checks for existing entries before inserting:

INSERT INTO Table2 (ID, Name)
SELECT source.ID, source.Name
FROM Table1 AS source
WHERE source.ID IN (1, 2, 4)
AND NOT EXISTS (
    SELECT 1 FROM Table2 AS target WHERE target.Name = source.Name
);

Both methods ensure you only add rows that don't conflict with the unique Name constraint.

Bonus: If Table1's Target Rows Have Duplicate Names #

If entries 1, 2, 4 in Table1 already have duplicate Name values, you'll want to deduplicate them first to avoid conflicts entirely. For example, to keep the row with the smallest ID:

-- Example for MySQL; adjust syntax slightly for other databases
INSERT INTO Table2 (ID, Name)
SELECT MIN(ID), Name
FROM Table1
WHERE ID IN (1, 2, 4)
GROUP BY Name;

This groups rows by Name and picks the smallest ID, ensuring only one entry per name gets inserted.

内容的提问来源于stack exchange，提问作者JDunn