Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
1. In case of Source file may contain unique records:
==> In this case, you can import all the files to the Destination table. And before inserting into Destination table use tMap to lookup with the source file unique column. Map the primary key from the inserting main flow to the primary unique column of lookup source file. And keep the 'Inner Join Reject' setting to True in tMap at the output side.
This will insert only those records which are not in the destination table if few of the records are already there is destination table, then it will reject based on an inner join.
1. In case of Source file may contain duplicate records:
==> I am assuming here the whole row does not have duplicated values, only a few columns have duplicated values.
For example: | Id | Name | Age | Country |
2 John 25 USA
5 John 28 USA
8 Jack 32 UAE
For above case use tAddCRCRow component while inserting data into dest table. And apply/check CRC for all columns, so each record will get a unique CRC value. After that before inserting data into Dest. table join the main flow with source file CRC value as lookup and keep the 'Inner Join Reject' to True at the output side in tMap.
This will insert only those records which are not in the destination table if few of the records are already there is destination table, then it will reject based on an inner join.
Don't forget to give kudos if this reply is worked for you. Also, suggestions/ improvements for this solutions are appreciated.
Hi @swapneel9218,
In this scenario we can create hashkeys for each record not for key column using MDM Hash Key Routine.
after creating hash keys for already inserted table and newly inserting table lookup those keys in tMap then we will get only the filtered new columns.
Example:
Data1: Data2:
Id Name Id Name
100 krane 101 Lunarg (updated)
101 Lunar 103 Harry (duplicate)
103 Harry 104 potter (new record)
105 Henry (new record)
so lets think there is no primary key then we create hash key for whole record,
then the hashkeys for example
Data1: | Data2:
Id | Name | hashkey1 | Id | Name | hashkey2
100 krane 1005867 | 101 Lunarg (updated) 5456878
101 Lunar 1239878 | 103 Harry (duplicate) 4354587
103 Harry 4354587 | 104 potter (new record) 5650112
| 105 Henry (new record) 1008867
so in the above example you can see the the hashkeys for 103 record it is same.
for updated record 101 it is different hash key. so based on this we lookup the hashkeys in tMap get the rejected columns in output.i.e, in target Table D.
Don't forget to give kudos if this reply is worked for you. Also, suggestions/ improvements for this solutions are appreciated.