在 SQLServer 中避免 INSERTINTOSELECT 查询中的重复

我有以下两张桌子:

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


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

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

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

然而，在我的例子中，重复的 ID 可能存在于 Table2中(在我的例子中，它只是“ 1”) ，我不想再次复制它，因为那样会抛出一个错误。

我可以这样写:

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

407250 次浏览
小开
最佳答案

使用 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的效率较低。

小开

在 MySQL 中你可以这样做:

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

SQLServer 有类似的东西吗？

小开

在唯一索引 就像 IanC 建议的那样上使用 ignore Duplicates是我对类似问题的解决方案，使用 OptionWITH IGNORE_DUP_KEY创建索引

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

参考编号: Index _ option

小开

我也遇到过类似的问题 DISTINCT 关键字有魔力:

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

在 SQLServer 中，可以为(需要唯一的列)的表设置 独一无二的钥匙索引

From sql server right click on the table design select Indexes/Keys

Select column(s) that will be not duplicate , then type Unique Key

小开

有点偏离主题，但是如果您想要将数据迁移到一个新的表，并且可能的重复数据在 原来的桌子中，并且可能重复的列不是 id，那么使用 GROUP BY就可以了:

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

我最近也遇到了同样的问题。
以下是我在微软 SQL 服务器2017年的工作..。
主键应该设置在表2中的 ID 上..。
当然，两个表之间的列和列属性应该是相同的。这将在您第一次运行下面的脚本时起作用。表1中的重复 ID 将不会插入..。

如果您第二次运行它，您将得到一个

违反 PRIMARYKEY 约束错误

这是密码:

Insert into Table_2
Select distinct *
from Table_1
where table_1.ID >1
小开

在 INSERT之前一个简单的 DELETE就足够了:

DELETE FROM Table2 WHERE Id = (SELECT Id FROM Table1)
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1

将 Table1切换为 Table2取决于要保留哪个表的 Id和 name对。

小开

在我的例子中，我在源表中有重复的 ID，所以没有一个提案是有效的。我不在乎表现，就这么一次。 为了解决这个问题，我用一个光标一个接一个地取出记录，以忽略重复的记录。

下面是一个代码示例:

DECLARE @c1 AS VARCHAR(12);
DECLARE @c2 AS VARCHAR(250);
DECLARE @c3 AS VARCHAR(250);




DECLARE MY_cursor CURSOR STATIC FOR
Select
c1,
c2,
c3
from T2
where ....;


OPEN MY_cursor
FETCH NEXT FROM MY_cursor INTO @c1, @c2, @c3


WHILE @@FETCH_STATUS = 0
BEGIN
if (select count(1)
from T1
where a1 = @c1
and a2 = @c2
) = 0
INSERT INTO T1
values (@c1, @c2, @c3)


FETCH NEXT FROM MY_cursor INTO @c1, @c2, @c3
END
CLOSE MY_cursor
DEALLOCATE MY_cursor
小开

我使用 MERGE 查询来填充表，而不会出现重复。 我遇到的问题是表中的双键(Code，Value) , 而且存在的查询非常慢 MERGE 执行得非常快(超过 X100)

MERGE 查询实例

小开

对于一个表，当从多个字段创建一个唯一索引时，它可以很好地工作。如果7个字段中的所有字段(在本例中)都具有相同的值，那么简单的“ INSERT IGNORE”将忽略重复字段。

在 PMA 结构视图中选择字段并单击“唯一”，将创建新的组合索引。

enter image description here


提交答案