Insert/Update using Join Snap

[pranil2k2]

Hi,

I am using Join Snap, to implement the logic of Insert new record and update existing one by doing join on target table. i defined left outer join to read all input records and in router i used condition on input1_column1, the condition is working for existing records and it not working for new records. Please can some one can help me how to implement Insert/Update logic using Join snap.

[RogerSramkoski]

Based on what you are trying to accomplish, I suggest taking a look at our Diff snap. You can see a screenshot of the snap below. The two inputs are labeled to help clarify that the lower input is for the original and the outputs are also labeled so you can build separate streams for handling the possible outcomes. If you have a specific reason for trying to build the logic yourself with Join and Router, please let us know.

[pranil2k2]

Hi,

Thanks for the information. Two thing I want to ask you for advice.

1. I went through Diff Snap, can I know whether it will use any key column to compare between two files?
2. this is regarding Join Snap, what is the use of Left Outer Join? if i cant differentiate new and old records? the inner join will give only matched records, the left outer join has to give both matched and new recrods right? then it will be easy to do insert/update.

Thanks in advance.

Anil

[RogerSramkoski]

Hello Anil,

The Diff snap looks at the entire record for comparison, not a single key. The documentation linked in the previous reply has sample pipelines to download then import, which can help you understand how the snap will function.

Regarding your second question, left outer join will take all the data from input0 and input1 that did not match. Our Join snap documentation has an example of what a data stream looks like for each join type.

[pranil2k2]

Hi,

Thanks. My requirement is to work on single Key column. here in my case i am using WRxx as key column. so i thought of using Join snap with left outer join. I got your info on this join type, that is the my idea too. when i use left outer join, i will get all records from Input0 and matched records from Input1 and for unmatched records it should retrun null as out put and i thought of using Null records as New records and matched records as update records. so, if u have any job on join snap, please advice.

Thanks in advance.