在 SQL Server 中的 INSERT INTO SELECT 查询中避免重复 [英] Avoid duplicates in INSERT INTO SELECT query in SQL Server

问题描述

我有以下两个表:

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

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

我需要将数据从 Table1 插入到 Table2.我可以使用以下语法:

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

但是，就我而言，Table2 中可能存在重复的 ID(在我的情况下，它只是1")，我不想再次复制它因为那会抛出错误.

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.

我可以这样写:

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 语句.

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.

推荐答案

Using 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 效率较低.见 此链接了解更多详情.

Of the three options, the LEFT JOIN/IS NULL is less efficient. See this link for more details.

这篇关于在 SQL Server 中的 INSERT INTO SELECT 查询中避免重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！