0 votes
1 view
in SQL by (20.9k points)

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.

1 Answer

0 votes
by (36.7k points)

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/

...