How to Efficiently Avoid Duplicates When Using INSERT INTO SELECT in SQL Server?

The INSERT INTO SELECT statement in SQL Server efficiently avoids duplicate data

In database operations, inserting data from one table to another while avoiding duplication is a common task. This article discusses how to efficiently add records in Table1 to Table2 to avoid ID duplication.

One way is to use the IF-ELSE statement to check whether the ID exists in Table2 before inserting:

<code class="language-sql">IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
  INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 
ELSE
  INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id1</code>

However, this method requires writing multiple INSERT INTO-SELECT statements based on conditions. In search of a more efficient solution, we explore three alternatives:

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>

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>

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>

Among these three methods, the LEFT JOIN/IS NULL method is less efficient than the other methods. For inserting data in SQL Server's INSERT INTO SELECT query while avoiding duplication, NOT EXISTS and NOT IN techniques provide the best performance.

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