什么是"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?

这是我在做什么:

1. 使用"OpenQuery"从MySQL拉记录以定义MySQL"A.record_id"
2. 从此处将这些记录具有特定条件而不是直接ID"加入MS SQL Server中的记录中，我在SQL Server中找到了一个新的相关"B.new_id"记录标识符.
3. 我想将找到的结果插入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屋！