避免重复插入到SQL Server中的SELECT查询中

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 Table1 to Table2. I can use the following syntax:

我需要将数据从表1插入到表2。我可以使用以下语法:

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-SELECT statements based on some condition.

有没有更好的方法来做到这一点而不用使用IF - ELSE?我希望避免基于某些条件的两个INSERT INTO-SELECT语句。

6 个解决方案

#1

151

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)

Using 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:

使用左连接/为空:

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 NULL is less efficient. See this link for more details.

在这三个选项中，左连接/为NULL的效率较低。有关更多细节，请参见此链接。

#2

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有什么相似之处吗?

#3

I just had a similar problem, the DISTINCT keyword works magic:

我有一个类似的问题，独特的关键字工作魔术:

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

#4

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

在唯一索引上使用忽略重复(如IanC建议的)是我解决类似问题的方法，使用IGNORE_DUP_KEY选项创建索引

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

Ref.: index_option

裁判:index_option

#5

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

从SQL Server中，您可以在表上为(需要惟一的列)设置惟一的键索引

#6

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:

有点偏离主题，但是如果您想将数据迁移到新的表中，并且可能的重复项在原始表中，并且可能重复的列不是id，那么组BY将会这样做:

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

#1

151