Avoid duplicates in INSERT INTO SELECT query in SQL Server

Question

I have the 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. I can use the following syntax:

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

However, in my case, duplicate IDs might exist in Table2 (in my case, it's 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 1

Try using NOT EXISTS like this:

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)

You can use NOT IN this way:

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)

Use LEFT JOIN/IS NULL this way:

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

In all the above three options, the LEFT JOIN/IS NULL is less efficient. 

Refer to this link for more details:

https://web.archive.org/web/20160518211340/https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL training.

Answer 2

To avoid duplicates when inserting from one table to other table.Use the following query

Insert into table2(id,name)

Select t1.id,t1.name from table1 

Where not exists (select 1 from table2 where table2.id=table1.id)

Here, using a sub query with not exists clause to check whether the id of table2 is present in table1 or not.