Transform Map to insert and update multiple record in target table for a single record in source table based on values in source and target values.

[Suraj Nepal]

I have a source table that has multiple columns with multiple comma separated value.

Target table can only have 4 entry for each user.

1.First I need to determine how many of these 4 entry need to be created for single record in source table.

2. If all4 record need to be created I should insert all 4 record.

3. if some of them eg. 2 of them exist and 2 of them do not exist then, I have to update two record and insert rest of two records.

4. if all 4 record exist then I need to updated each of them and updated target columns.

The problem here is there is no single value for Coalesce  that I can rely on.

I have to do multiple insert and update in target record for single record in source table.

Can someone please suggest me what would be the approach I should follow. 

Thank you in advance.

[Tony Chatfield1]

Hi, I would consider using a before transform script.
This will allow you the flexibility to script your solution inserting, updating or aborting these actions for multiple record across multiple tables based on row content and existing data.
The only real downside is the import-set record will have no clear details of the transform delivering an insert\update\target record, but as you potentially have multiple target records for a source field this would never have been accurate anyway.

Add onBefore scripts to the transform map (servicenow.com)