update existing rows and add new ones

Dear your help with a problem,

I am currently filling a dimension of a data warehouse and what I want is to insert the new values ​​and if there are updates, I searched the web for a node that could help me and I found the DB MERGE node but it does not give me the results I want. duplication of data.

Your help, thank you very much.


I think that duplication is a wrong assumption. Merge updates existing records based on primary key and adds not existing records. To add duplicates you need to play with primary key.

Thanks for answering,

The table I am filling is the following.


So you mean you should restrict from the database?

In DB Merge you use columns for Where clause. They are represent business PK. The duplicates are considered against PK. Your table PK is bogus as usually in DW.

Thanks again for the response,

I tell you that I have done an insertion test simulating a record but the new ones are not inserted.

My node configuration is as follows:

The “PK” of my table are the same as in the where of the merge node.

Thank you in advance for your kind help, because I am stuck on this issue, or in turn an example to guide me.

This sounds that row with COD_PAIS = 100, COD_PROVINCIA = 100, COD_CANTON=100 and COD_PARROQUIA=100 alredy exists in the table.
So, the merge will update this record. PK is specifically prevents duplicates.