PDA

View Full Version : how can I do increment copy with spoon?



linpch
04-12-2006, 06:39 PM
hi matt,
I want to do the example as fllows:
the person table:
id name roleid
1 hello 2
2 good 2
3 linpch 1


and use the Insert/update or update,the result table
id name roleid
1 hello 2
2 good 2
3 linpch 1
I update the person table item:3 linpch 2,the result which I want is good.
but I want delete person table item: 2 good 2,the result table can not delete that item,
I really want synchronization update. that is the person table add,update,delete, and the result table can do also.
how can I do what I want?
thank you!

MattCasters
04-12-2006, 10:54 PM
Hi Lin,

There is a delete step available in the Development tree and together with the Merge step you can compare current and previous versions of a table. That should give you all the building blocks to do synchronisations, but I don't have the time to figure it out for you at the moment.

BTW: Putting Delete in Insert/Update doens't make sense because you don't have the key to delete as input row because the rows is deleted in the source system of-course!

Think about it, it makes sense!

All the best,

Matt

kettle_anonymous
04-13-2006, 06:30 AM
I know what you tell me,but you say that the merge step have the two input,the compared and the reference table ,I think the refernce table is the current table ,the compare is the previous table,is it right?and I have no idea the next step how I do it.can you give me the ktr image?thank you!

MattCasters
04-13-2006, 06:39 AM
>can you give me the ktr image?

Sorry, no, that's your job... Please do consider examining the problem first.
To keep two tables in sync with regards to the deleted records, you need to detect the deleted records in the first place.

One POSSIBLE solution I offer is to do extraction from the source table periodically and keep the previous version of that data.
Then you use the Merge step to compare the two versions.
Merge will flag a record as deleted, new, changed of identical using the primary key of the table.

I suggest you direct new and changed records to an Insert/Update step (using a filter) and the deleted records to a delete step.

I suggest you use Chef to embed the logic of keeping the previous version of the extracted data.

Good luck,
Matt