通过选择插入时如何在插入表之前检查重复项 [英] How can I check for duplicates before inserting into a table when inserting by select

问题描述

通过选择插入时如何在插入表之前检查重复项:

How can I check for duplicates before inserting into a table when inserting by select:

insert into table1
select col1, col2 
from table2

我需要检查 table1 是否已经有一行 table1.col1.value = table2.col1.value，如果是，则从插入中排除该行.

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.

推荐答案

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 

替代使用除了

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 

使用 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)

这篇关于通过选择插入时如何在插入表之前检查重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！