CODEWITHSUNDEEP
mysqlsql-server-2012etlinformatica
Huzaifa M Aamir
Huzaifa M Aamir

Reputation: 383

How to create a automatic process to update record when one field is changed

I have been trying to figure this out for some time and needed your help on how to achieve. I have created an ETL process in informatica that takes users from sql server (source db) and adds them in a mysql (target db) The process involves a number of steps.

Step 1. - Moves source.dbo.[user] (SQL SERVER) to target.user The user table looks something like this

userid   username  profile  disabled
1        A         A         0
2        B         A         0
3        c         B         1

This table is upserted when a disabled status is changed and when a new record is added

Step 2 - The new users are then inserted in to target.worker table; In order to only add users that dont exist in the worker table a left join is used:

select wrk.*, usr.* from target.worker 
wrk left join target.user usr on wrk.userid=usr.userid 
where wrk.usrid is null;

The worker table looks something like this

id   username   userid   disabled
1    A          1        0
2    B          A        0

Then after the process runs userid 3 will be added to the worker table

Now here comes the part I cant figure out: I need to update the users in the worker table when their disabled status changes as well. Currently I have mapped the set up to add workers from the user on left join to add only those users that dont exist in the current worker table. However how can I map it so it updates those records in the worker table when the disabled status changes in the corresponding user table

I hope this makes sense. To make it simpler:

When target.[user] is 

userid   username  profile  disabled
1        A         A         0
2        B         A         0
3        c         B         1
4        D         C         0

and target.worker is 

userid   username  profile  disabled
1        A         A         0
2        B         A         0
3        c         B         0

I need a join query with a filter or explanation process so that the worker table both inserts the new record and changes the an existing records disabled status: So it will look like this

 userid   username  profile  disabled
    1        A         A         0
    2        B         A         0
    3        c         B         1 <--- Record updated
    4        D         C         0 <-- New record added

Upvotes: 0

Views: 97

Answers (1)

Maciejg
Maciejg

Reputation: 3353

Use a Lookup on target.worker to verify if the record already exist. If it does an NewLookupRow will be 2 (update), use DD_UPDATE in Update Transformation. If it's 1, use DD_INSERT. Use a NewLookupRow != 0 Filter before the Update Trans to skip the rows you already have in target.

Upvotes: 1

Related Questions