SQL Server의 INSERT INTO SELECT 쿼리에서 중복 방지

SQL Server의 INSERT INTO SELECT 쿼리에서 중복 방지

---

다음 두 테이블이 있습니다.

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

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

에서 Table1에 데이터를 삽입해야합니다 Table2. 다음 구문을 사용할 수 있습니다.

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

그러나 제 경우에는 중복 ID가 존재할 수 있으며 Table2(제 경우에는 " 1" 일뿐 ) 오류가 발생할 수 있으므로 다시 복사하고 싶지 않습니다.

다음과 같이 작성할 수 있습니다.

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

사용하지 않고이 작업을 수행하는 더 좋은 방법이 IF - ELSE있습니까? INSERT INTO-SELECT조건에 따라 두 가지 진술 을 피하고 싶습니다 .

---

사용 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)

사용 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)

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

세 가지 옵션 중 LEFT JOIN/IS NULL효율성이 떨어집니다. 자세한 내용은 이 링크를 참조하십시오 .

---

MySQL에서는 다음을 수행 할 수 있습니다.

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

SQL Server에도 비슷한 것이 있습니까?

---

비슷한 문제가 있었는데 DISTINCT 키워드가 마술처럼 작동합니다.

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

---

Using ignore Duplicates on the unique index as suggested by IanC here was my solution for a similar issue, creating the index with the Option WITH IGNORE_DUP_KEY

In backward compatible syntax
, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

Ref.: index_option

---

From SQL Server you can set a Unique key index on the table for (Columns that needs to be unique)

---

I was facing the same problem recently...
Heres what worked for me in MS SQL server 2017...
The primary key should be set on ID in table 2...
The columns and column properties should be the same of course between both tables. This will work the first time you run the below script. The duplicate ID in table 1, will not insert...

If you run it the second time, you will get a

Violation of PRIMARY KEY constraint error

This is the code:

Insert into Table_2
Select distinct *
from Table_1
where table_1.ID >1

---

A little off topic, but if you want to migrate the data to a new table, and the possible duplicates are in the original table, and the column possibly duplicated is not an id, a GROUP BY will do:

INSERT INTO TABLE_2
(name)
  SELECT t1.name
  FROM TABLE_1 t1
  GROUP BY t1.name

---

A simple DELETE before the INSERT would suffice:

DELETE FROM Table2 WHERE Id = (SELECT Id FROM Table1)
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1

Switching Table1 for Table2 depending on which table's Id and name pairing you want to preserve.

참고URL : https://stackoverflow.com/questions/2513174/avoid-duplicates-in-insert-into-select-query-in-sql-server