How to Efficiently Avoid Duplicates When Inserting Data Using SQL Server's INSERT INTO SELECT?

SQL Server: Preventing Duplicate Records During INSERT INTO SELECT

Inserting data from one table to another in SQL Server requires careful consideration to avoid duplicate entries. This is crucial when the destination table already contains potentially conflicting data. Consider this example:

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

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

A simple INSERT INTO SELECT would fail due to the duplicate ID (1). While conditional IF-ELSE statements are possible, they're inefficient and cumbersome. More efficient alternatives exist:

Method 1: Using NOT EXISTS

This is generally the most efficient approach:

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

Only rows from Table1 where the ID doesn't already exist in Table2 are inserted.

Method 2: Using NOT IN

This method filters duplicates during the selection process:

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

This approach is less efficient than NOT EXISTS, particularly with large datasets.

Method 3: Using LEFT JOIN and IS NULL

This is typically the least efficient method:

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

A LEFT JOIN identifies unmatched rows; only those with a NULL value in Table2.id are inserted.

For optimal performance, especially with large datasets, the NOT EXISTS method is recommended. Avoid the LEFT JOIN approach unless other factors make it preferable. Choose the method that best suits your specific needs and dataset size.

The above is the detailed content of How to Efficiently Avoid Duplicates When Inserting Data Using SQL Server's INSERT INTO SELECT?. For more information, please follow other related articles on the PHP Chinese website!