Scenario 3:

How to segregate the duplicate and distinct rows from source table to separate target tables?

source table:

 

COL1 COL2 COL3
a b c
x y z
a b c
r f u
a b c
v f r
v f r

Target Table 1: Table containing all the unique rows

COL1 COL2 COL3
a b c
x y z
r f u
v f r

 

Target Table 2: Table containing all the duplicate rows

 

COL1 COL2 COL3
a b c
a b c
v f r

Solution:

Step  1: Drag  the source to mapping and connect it to an aggregator transformation.

scenario 3 src to aggr

Step  2: In aggregator transformation, group by the key column and add a new port  call it count_rec to count  the key column.

Step  3: connect  a router to the  aggregator from the previous step.In router make two groups one named "original" and another as "duplicate"
In original write count_rec=1 and in duplicate write count_rec>1.

scenario 3 aggr to router

The picture below depicting group name and the filter conditions

scenario router grouping

Step 4: Connect two group to corresponding target table.

Scenario 3 router to tgt

See Scenario 10 as a different approach for similar situation.