How to Avoid Duplicate Rows in SQL Server's INSERT INTO SELECT Without IF-ELSE Statements?

Methods to avoid duplicate data in SQL Server INSERT INTO SELECT statement

When using the INSERT INTO SELECT statement, it is important to prevent duplicate data insertion. This article describes several effective ways to avoid such problems in SQL Server without using conditional statements such as IF-ELSE.

Method 1: Use NOT EXISTS

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

This query prevents duplicate insertions by checking if a matching ID exists in TABLE_2. If it already exists, skip this line.

Method 2: Use NOT IN

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

Similar to NOT EXISTS, the NOT IN clause also excludes IDs that already exist in TABLE_2. It checks if the ID in TABLE_1 is in TABLE_2 and inserts only if not present.

Method 3: Use LEFT JOIN/IS NULL

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

Use LEFT JOIN and IS NULL to more clearly identify the rows that need to be inserted. This query selects and inserts all rows in TABLE_1 that do not have a matching ID in TABLE_2.

Performance comparison

In terms of performance, LEFT JOIN/IS NULL is generally not as efficient as NOT EXISTS and NOT IN. This is because LEFT JOIN requires scanning more tables and rows and consumes more resources. For large data sets, it is recommended to use NOT EXISTS or NOT IN.

The above is the detailed content of How to Avoid Duplicate Rows in SQL Server's INSERT INTO SELECT Without IF-ELSE Statements?. For more information, please follow other related articles on the PHP Chinese website!