Ashish Gupta Ashish Gupta - 4 months ago 15
SQL Question

How to avoid duplicates in INSERT INTO SELECT query in SQL Server?

I have following two tables:-

Table1
-------------
ID Name
1 A
2 B
3 C

Table2
--------
ID Name
1 Z


I need to insert data from
Table1
to
Table2
and I can use following syntax for the same:-

INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1


However, In my case duplicate Ids might exist in
Table2
(In my case Its Just "
1
") and I don't want to copy that again as that would throw an error.

I can write something like this:-

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.Id<>1


Is there a better way to do this without using
IF - ELSE
? I want to avoid two
INSERT INTO-SELECT
statements based on some condition.

Answer

Using NOT EXISTS:

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)

Using NOT IN:

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)

Using LEFT JOIN/IS NULL:

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

Of the three options, the LEFT JOIN/IS NULL is less efficient. See this link for more details.