Need help writing unique entries to a database

[JayZ1]

I am getting data from an MSSQL database and writing it out to a MySQL database, however I want this job to only bring in new data entries, so in the database I have set the column as a unique field.

in this case every time the job runs it will get existing entries and new ones from the MSSQL database, however my expectation is that it should get all of the new entries and bring them into MYSQL database.

I can think of using a tmap to see if entries exist otherwise if they don't bring them in, however I feel like there should be a default option on the database component itself to help with this? Any ideas or suggestions? With the current setup it fails because it detects existing entries already and stops the job instead of going through all of the entries and bring in new ones.

[Anonymous]

Hi

On tMssqlOutput component, select Insert if not exist option in the action on data table, this option add new entries to the table if they do not exist.

 

Regards

Shong

[JayZ1]

Hi Shong, That does not show on my MySQL database component.

[Anonymous]

@Harjot Toor​ sorry, I thought you used MSSQL Server DB, with MYSQL database, you can try insert ignore option, this option will ignore the error such as duplicate entries keys and continue to insert the rest records. If you want to get the entries which don't exist in target DB and only insert these non-existing entries into target DB, you need to do an inner join between the source data and all of target data, please refer to this KB article.

Regards

Shong

[JayZ1]

So I have added a lookup from the source database in which I do a lookup. What other things should I do to complete this so it brings in only new entries?

[Anonymous]

@Harjot Toor​ , all entries from the target DB will be lookup table, the source data will be main flow. Take a look at the KB article to learn how to get the unmatched rows, the unmatched rows are the entries which does not exist in target DB.