什么是"INSERT IGNORE"?等价于MS SQL Server? [英] What is a "INSERT IGNORE" equivalent in MS SQL Server?
问题描述
我正在尝试使用"OPENQUERY"将记录从MS SQL Server插入MySQL数据库中,但是我试图做的是忽略重复的键消息.因此,当查询遇到重复项时,请忽略它并继续进行.
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.
我该怎么做才能忽略重复项?
What ideas can I do to ignore the duplicates?
这是我在做什么:
- 使用"OpenQuery"从MySQL拉记录以定义MySQL"A.record_id"
- 从此处将这些记录具有特定条件而不是直接ID"加入MS SQL Server中的记录中,我在SQL Server中找到了一个新的相关"B.new_id"记录标识符.
- 我想将找到的结果插入MySQL的新表中,例如A.record_id,B.new_id.在新表中,我将A.record_id设置为该表的主键.
问题是,当有时将表A与表B连接起来时,我在表B中找到2+条记录,这些记录与我要查找的条件匹配,这导致我的数据集中的A.record_id值达到2+倍,然后再插入该记录到导致问题的表A中.注意,我可以使用聚合函数来消除记录.
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.
推荐答案
我认为没有特定的选择.但这很容易做到:
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)
如果有一组以上的唯一键,则可以添加其他not exists
语句.
If there is more than one set of unique keys, you can add additional not exists
statements.
对于修订后的问题:
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);
row_number()
函数为一组行中的每一行分配一个顺序号.该组由partition by
语句定义.数字从1开始并从那里开始递增. order by
子句说您不在乎顺序.正好每个ID的一行的值为1.重复的行的值大于1. seqnum = 1
每个ID恰好选择一行.
The row_number()
function assigns a sequential number to each row within a group of rows. The group is defined by the partition by
statement. The numbers start at 1 and increment from there. The order by
clause 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 = 1
chooses exactly one row per id.
这篇关于什么是"INSERT IGNORE"?等价于MS SQL Server?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!