How Can I Prevent Duplicate Entries When Using INSERT INTO SELECT in SQL Server?

Avoiding Duplicate Data with SQL Server's INSERT INTO SELECT

When using INSERT INTO SELECT in SQL Server, duplicate entries can easily arise if the target table already contains the data being inserted. To prevent this, efficient filtering is crucial before insertion.

Let's illustrate with an example. We want to insert data from Table1 into Table2, but avoid duplicate IDs:

<code>Table1:
----------
ID   Name
1    A
2    B
3    C

Table2:
----------
ID   Name
1    Z</code>

Using IF-ELSE statements is possible, but becomes unwieldy and requires multiple INSERT INTO statements.

Efficient Solutions: NOT EXISTS, NOT IN, and LEFT JOIN

More efficient methods utilize NOT EXISTS, NOT IN, and LEFT JOIN to filter out pre-existing data in Table2 before insertion.

1. Using NOT EXISTS:

This approach checks for the existence of each row from Table1 in Table2 before inserting.

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

2. Using NOT IN:

Similar to NOT EXISTS, NOT IN compares IDs, only inserting rows where the ID isn't found in Table2.

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

3. Using LEFT JOIN with IS NULL:

A LEFT JOIN identifies rows in Table1 lacking a match in Table2. IS NULL filters for these unmatched rows.

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;

While all three methods achieve the same outcome, NOT EXISTS generally offers superior performance, especially with large datasets, and is therefore the recommended approach.

The above is the detailed content of How Can I Prevent Duplicate Entries When Using INSERT INTO SELECT in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!