How to ignore duplicate keys when extracting data using OPENQUERY while joining two tables?

Question

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?

Here is what I am doing:

1. pulling records from MySQL using "OpenQuery" to define MySQL "A.record_id"
2. 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.
3. 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.

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.

Toast

@MartinSmith I removed the confusing part form my question. I tried to using the IGNORE_DUP_KEY but I get a syntax error. I don't know if I can use it with openquery. here is my use case INSERT IGNORE_DUP_KEY OPENQUERY – Toast

IGNORE_DUP_KEY is a property of an index. Not a key word that you use in a query. It is not relevant unless you are inserting to SQL Server hence request for clarification on direction of transfer. – Towery

Answer 1

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 exists statements.

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 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.

Unify

thanks for that note. The problem is that with in my select I may be generating duplicates and when inserting 2 records with the same unique value I get an error because originally the records do not exists but when trying to insert the value 2 different times then i encounter the error. – Toast

Can you please explain the second query? I never used over (partition by ..... I don't know what it is and what it does. I need to know how can I apply it to my query.. – Toast

This is still now working for me. I re-stated my question to explain the problem that I am running into. please review that. I think your answer is on the right track – Toast

I think the row_number() over..... statment is not working as you are expecting because I see 2 rows with the same id and have a value 1 as – Toast

@Mike . . . That seems highly unlikely. I would suggest that if you are really seeing this, ask another question with the sample data and query you are using. – Unify

"it is easy enough to do" -- except that isn't at all as easy as INSERT IGNORE. It is actually quite obnoxious. – Laurin

Answer 2

If you are on SQL Server 2008+, you can use MERGE to do an INSERT if row does not exist, or an 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)

Noemi

It looks like he is trying to insert records to MySql from MS Sql with OPENQUERY. I don't think merge is going to help at all in this case. – Dimitrovo

Question and Title are confusing, and opposite. I looked at title where @Mike asks for INGORE INSERT equivalent of MySQL, in SQL Server. Whereas Question is saying opposite, i.e. inserting into MySQL from SQL Server. – Noemi