MySQL 什么是 MS SQL Server 中的“INSERT IGNORE”等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21220753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is a "INSERT IGNORE" equivalent in MS SQL Server?
提问by Jaylen
I am trying to insert records into MySQL database from a MS SQL Server using the "OPENQUERY" but what I am trying to do is ignore the duplicate keys messages. so when the query run into a duplicate then ignore it and keep going.
我正在尝试使用“OPENQUERY”将记录从 MS SQL Server 插入 MySQL 数据库，但我想要做的是忽略重复的键消息。所以当查询遇到重复时，请忽略它并继续。
What ideas can I do to ignore the duplicates?
我可以做些什么来忽略重复项？
Here is what I am doing:
这是我在做什么：
- pulling records from MySQL using "OpenQuery" to define MySQL "A.record_id"
- Joining those records to records in MS SQL Server "with a specific criteria and not direct id" from here I find a new related "B.new_id" record identifier in SQL Server.
- I want to insert the found results into a new table in MySQL like so A.record_id, B.new_id Here in the new table I have A.record_id set as a primary key for that table.
- 使用“OpenQuery”从 MySQL 中提取记录来定义 MySQL“A.record_id”
- 将这些记录与 MS SQL Server 中的记录“具有特定条件而不是直接 id”从这里我在 SQL Server 中找到了一个新的相关“B.new_id”记录标识符。
- 我想将找到的结果插入到 MySQL 中的新表中，例如 A.record_id, B.new_id 在新表中，我将 A.record_id 设置为该表的主键。
The problem is that when joining table A to Table B some times I find 2+ records into table B matching the criteria that I am looking for which causes the value A.record_id to 2+ times in my data set before inserting that into table A which causes the problem. Note I can use aggregate function to eliminate the records.
问题是，有时将表 A 连接到表 B 时，我在表 B 中找到 2+ 条记录，与我正在寻找的条件匹配，这会导致数据集中的值 A.record_id 在将其插入表 A 之前达到 2+ 次这导致了问题。注意我可以使用聚合函数来消除记录。
回答by Gordon Linoff
I don't think there is a specific option. But it is easy enough to do:
我认为没有特定的选择。但它很容易做到：
insert into oldtable(. . .)
select . . .
from newtable
where not exists (select 1 from oldtable where oldtable.id = newtable.id)
If there is more than one set of unique keys, you can add additional not existsstatements.
如果有不止一组唯一键，您可以添加其他not exists语句。
EDIT:
编辑：
For the revised problem:
对于修改后的问题：
insert into oldtable(. . .)
select . . .
from (select nt.*, row_number() over (partition by id order by (select null)) as seqnum
from newtable nt
) nt
where seqnum = 1 and
not exists (select 1 from oldtable where oldtable.id = nt.id);
The row_number()function assigns a sequential number to each row within a group of rows. The group is defined by the partition bystatement. The numbers start at 1 and increment from there. The order byclause says that you don't care about the order. Exactly one row with each id will have a value of 1. Duplicate rows will have a value larger than one. The seqnum = 1chooses exactly one row per id.
该row_number()函数为一组行中的每一行分配一个序列号。该组由partition by语句定义。数字从 1 开始并从那里递增。该order by条款说您不关心订单。每个 id 正好有一行的值为 1。重复行的值将大于 1。在seqnum = 1每个ID选一行。
回答by Barb C. Goldstein
If you are on SQL Server 2008+, you can use MERGEto do an INSERTif row does not exist, or an UPDATE.
如果你的SQL Server 2008+，你可以用MERGE做一个INSERT，如果行不存在，或UPDATE。
Example:
例子：
MERGE
INTO dataValue dv
USING tmp_holding_DataValue t
ON t.dateStamp = dv.dateStamp
AND t.itemId = dv.itemId
WHEN NOT MATCHED THEN
INSERT (dateStamp, itemId, value)
VALUES (dateStamp, itemId, value)
