Good day.

It is necessary to transfer data from a MS SQL Server Express table to a third-party table in MySQL.

Trying to execute the following query, but it creates duplicate records. How can they be avoided?

INSERT INTO OPENQUERY(YAM, 'SELECT * FROM ia.recognized_numbers') (numcar, time, type) SELECT param0, date, objid FROM i.dbo.PROTOCOL WHERE action = 'NUMBER_DETECTED' AND date not IN (SELECT time FROM OPENQUERY(YAM, 'SELECT * FROM ia.recognized_numbers'))
  • Ie, as I understand it, you already have some records in the target table. "creates duplicate records" duplicated with what, with those that were already in the target table before the insert, or the inserted records themselves contain duplicates? - i-one
  • When inserting creates duplicates. As if the condition <code> AND date not IN </ code> is not satisfied at all. - drstannum
  • In the target table, which set of columns should be unique? - i-one

1 answer 1

The problem was solved by introducing an additional unique column. Records were duplicated due to the fact that the MSSQL Server timestamp stored in the form of 2016-05-30 01:44:36.043 , and in MySQL the record came in as 2016-05-30 01:44:36.000 . Because of this, the time column could not be used as unique because of the difference in the record.

Ps: Thanks for the tip @ i-one