SQL 通过选择插入时如何在插入表之前检查重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5599874/
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
How can I check for duplicates before inserting into a table when inserting by select
提问by Prabhu
How can I check for duplicates before inserting into a table when inserting by select:
通过选择插入时，如何在插入表之前检查重复项：
insert into table1
select col1, col2
from table2
I need to check if table1 already has a row with table1.col1.value = table2.col1.value, and if yes, then exclude that row from the insert.
我需要检查 table1 是否已经有一行 table1.col1.value = table2.col1.value，如果是，则从插入中排除该行。
回答by Conrad Frix
INSERT INTO table1
SELECT t2.col1,
t2.col2
FROM table2 t2
LEFT JOIN table1 t1
ON t2.col1 = t1.col1
AND t2.col2 = t1.col2
WHERE t1.col1 IS NULL
Alternative using except
替代使用除了
INSERT INTO @table2
SELECT col1,
col2
FROM table1
EXCEPT
SELECT t1.col1,
t1.col2
FROM table1 t1
INNER JOIN table2 t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2
Alternative using Not Exists
使用 Not Exists 的替代方法
INSERT INTO table2
SELECT col1,col2
FROM table1 t1
WHERE
NOT EXISTS( SELECT 1
FROM table2 t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2)
回答by John K.
insert into table1
select col1, col2
from table2
where table2.col1 not in (select col1 from table1)
回答by Sergei Golos
insert into table1
select distinct col1, col2
from table2
