在 SQL Server 中的 INSERT INTO SELECT 查询中避免重复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2513174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Avoid duplicates in INSERT INTO SELECT query in SQL Server
提问by Ashish Gupta
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 Table1to Table2. I can use the following syntax:
我需要从Table1to插入数据Table2。我可以使用以下语法：
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.
但是，在我的情况下，重复的 ID 可能存在Table2（在我的情况下，它只是“ 1”），我不想再次复制它，因为这会引发错误。
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-SELECTstatements based on some condition.
有没有更好的方法来做到这一点而不使用IF - ELSE？我想避免INSERT INTO-SELECT基于某些条件的两个语句。
回答by OMG Ponies
Using NOT EXISTS:
使用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:
使用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:
使用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 NULLis less efficient. See this link for more details.
回答by Duncan
In MySQL you can do this:
在 MySQL 中，您可以这样做：
INSERT IGNORE INTO Table2(Id, Name) SELECT Id, Name FROM Table1
Does SQL Server have anything similar?
SQL Server 有没有类似的东西？
回答by Hunter Bingham
I just had a similar problem, the DISTINCT keyword works magic:
我刚刚遇到了类似的问题， DISTINCT 关键字很神奇：
INSERT INTO Table2(Id, Name) SELECT DISTINCT Id, Name FROM Table1
回答by M. Salah
From SQL Server you can set a Unique keyindex on the table for (Columns that needs to be unique)
从 SQL Server，您可以在表上设置唯一键索引（需要唯一的列）
回答by Tazz602
Using ignore Duplicateson the unique index as suggested by IanC herewas my solution for a similar issue, creating the index with the Option WITH IGNORE_DUP_KEY
ignore Duplicates在IanC 建议的唯一索引上使用这里是我针对类似问题的解决方案，使用 Option 创建索引WITH IGNORE_DUP_KEY
In backward compatible syntax
, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.
Ref.: index_option
参考：index_option
回答by Vishane Naicker
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...
我最近
遇到了同样的问题......这是在 MS SQL Server 2017 中对我有用的东西......
主键应该设置在表 2 中的 ID 上......
列和列属性当然应该是相同的表。这将在您第一次运行以下脚本时起作用。表1中的重复ID，不会插入...
If you run it the second time, you will get a
如果你第二次运行它，你会得到一个
Violation of PRIMARY KEY constraint error
违反 PRIMARY KEY 约束错误
This is the code:
这是代码：
Insert into Table_2
Select distinct *
from Table_1
where table_1.ID >1
回答by FullStackFool
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 BYwill do:
有点题外话，但是如果你想将数据迁移到一个新表，并且可能的重复在原始表中，并且可能重复的列不是一个 id，aGROUP BY会做：
INSERT INTO TABLE_2
(name)
SELECT t1.name
FROM TABLE_1 t1
GROUP BY t1.name
回答by Sacro
A simple DELETEbefore the INSERTwould suffice:
一个简单DELETE的INSERT就足够了：
DELETE FROM Table2 WHERE Id = (SELECT Id FROM Table1)
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1
Switching Table1for Table2depending on which table's Idand namepairing you want to preserve.
开关Table1用于Table2取决于哪个表的Id和name配对要保留。
