避免 SQL Server 中 INSERT INTO SELECT 查询中的重复
我有以下两个表:
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
但是,就我而言,Table2
中可能存在重复的 ID(在我的情况下,它只是“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
语句。
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:
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.
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(11)
使用
NOT EXISTS
:使用
NOT IN
:使用
LEFT JOIN/IS NULL
:在三个选项中,
LEFT JOIN/IS NULL效率较低。请参阅此链接了解更多详细信息。
Using
NOT EXISTS
:Using
NOT IN
:Using
LEFT JOIN/IS NULL
:Of the three options, the
LEFT JOIN/IS NULL
is less efficient. See this link for more details.在 MySQL 中你可以这样做:
SQL Server 有类似的东西吗?
In MySQL you can do this:
Does SQL Server have anything similar?
我刚刚遇到了类似的问题,DISTINCT 关键字很神奇:
I just had a similar problem, the DISTINCT keyword works magic:
在唯一索引上使用
ignore Duplicates
正如 IanC 这里所建议的是我针对类似问题的解决方案,使用选项WITH IGNORE_DUP_KEY
创建索引参考:index_option
Using
ignore Duplicates
on the unique index as suggested by IanC here was my solution for a similar issue, creating the index with the OptionWITH IGNORE_DUP_KEY
Ref.: index_option
我最近遇到了同样的问题......
这是在 MS SQL Server 2017 中对我有用的东西......
主键应设置在表 2 中的 ID 上...
当然,两个表之间的列和列属性应该相同。这将在您第一次运行以下脚本时起作用。表1中重复的ID,不会插入...
如果你第二次运行它,你会得到一个
这是代码:
I was facing the same problem recently...
Heres what worked for me in MS SQL server 2017...
The primary key should be set on ID in table 2...
The columns and column properties should be the same of course between both tables. This will work the first time you run the below script. The duplicate ID in table 1, will not insert...
If you run it the second time, you will get a
This is the code:
在 SQL Server 中,您可以在表上为(需要唯一的列)
唯一键索引"nofollow noreferrer">
From SQL Server you can set a Unique key index on the table for (Columns that needs to be unique)
有点偏离主题,但是如果你想将数据迁移到新表,并且可能的重复项在原始表中,并且可能重复的列不是 id,而是
GROUP BY
会做: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,因此所有建议都不起作用。我不关心性能,只完成一次。
为了解决这个问题,我用光标逐一获取记录以忽略重复项。
这是代码示例:
In my case, I had duplicate IDs in the source table, so none of the proposals worked. I don't care about performance, it's just done once.
To solve this I took the records one by one with a cursor to ignore the duplicates.
So here's the code example:
我使用 MERGE 查询来填充表而不重复。
我遇到的问题是表中的双键(代码,值),
并且存在查询非常慢
MERGE 执行速度非常快(超过 X100)
MERGE 示例查询
I used a MERGE query to fill a table without duplications.
The problem I had was a double key in the tables ( Code , Value ) ,
and the exists query was very slow
The MERGE executed very fast ( more then X100 )
examples for MERGE query
对于一张表来说,从多个字段创建一个唯一索引时它可以完美地工作。如果 7 个字段(在本例中)全部具有相同的值,则简单的“INSERT IGNORE”将忽略重复项。
在PMA结构视图中选择字段并单击唯一,将创建新的组合索引。
For one table it works perfectly when creating one unique index from multiple field. Then simple "INSERT IGNORE" will ignore duplicates if ALL of 7 fields (in this case) will have SAME values.
Select fields in PMA Structure View and click Unique, new combined index will be created.
在
INSERT
之前执行一个简单的DELETE
就足够了:根据哪个表的
Id< 将
Table1
切换为Table2
/code> 和name
您想要保留的配对。A simple
DELETE
before theINSERT
would suffice:Switching
Table1
forTable2
depending on which table'sId
andname
pairing you want to preserve.