How to Avoid Duplicate Insertions in SQL INSERT INTO SELECT Statements?

Preventing Duplicate Rows in SQL INSERT INTO SELECT Statements

SQL Server's INSERT INTO SELECT statements can inadvertently introduce duplicate data into your target table. This can lead to data inconsistencies and errors. To avoid this, several techniques offer efficient solutions without resorting to multiple conditional INSERT INTO statements.

Methods to Eliminate Duplicate Inserts:

Here are effective strategies for handling potential duplicates during an INSERT INTO SELECT operation:

1. Utilizing NOT EXISTS:

This approach verifies if a record with a matching ID already exists in the target table. Only if no match is found will the new record be inserted.

<code class="language-sql">INSERT INTO TABLE_2 (id, name)
SELECT t1.id, t1.name
FROM TABLE_1 t1
WHERE NOT EXISTS (SELECT id FROM TABLE_2 t2 WHERE t2.id = t1.id);</code>

2. Employing NOT IN:

Similar to NOT EXISTS, this method excludes records from the insertion process if their IDs already exist in the destination table.

<code class="language-sql">INSERT INTO TABLE_2 (id, name)
SELECT t1.id, t1.name
FROM TABLE_1 t1
WHERE t1.id NOT IN (SELECT id FROM TABLE_2);</code>

3. Leveraging LEFT JOIN and IS NULL:

This technique uses a LEFT JOIN between the source and destination tables. If the ID from the source table is NULL in the joined result, it indicates the absence of a corresponding record in the destination table, allowing for safe insertion.

<code class="language-sql">INSERT INTO TABLE_2 (id, name)
SELECT t1.id, t1.name
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL;</code>

These methods efficiently prevent duplicate insertions by pre-checking for existing records. This maintains data integrity and avoids potential errors. Select the method that best aligns with your specific needs.

The above is the detailed content of How to Avoid Duplicate Insertions in SQL INSERT INTO SELECT Statements?. For more information, please follow other related articles on the PHP Chinese website!