Primary Key with Multiple Values 1

[Aalph]

Hello Everyone,

My Source data is structured as below,

Number (PK) Name State AppointedState Lines
1 Michael CO CO A
1 Michael CO CO H
2 George HI AZ A
2 George HI AZ H
3 Cindy AR AR H
3 Cindy AR CA A
3 Cindy AR CA H
3 Cindy AR IA A
3 Cindy AR IA H
3 Cindy AR ID A
3 Cindy AR KY A
3 Cindy AR OH A
4 Steven AR AZ A
4 Steven AR BA A

Since this is having multiple values of primary key, how should I go about it to do an update else insert strategy.

 

[blom0344]

Your real PK is a composite key consisting of Name State AppointedState Lines
What do you want to update/insert into this target?

Ties Blom

 

[Aalph]

Hi Blom,
Thanks for replying back.

For the 1st day, Target is null and I insert all the records. Now for the 2nd day(problem starts here), Number(1) has an update to Appointment State, updated to ‘FL’ and there are 2 new records, Number(5). Other records, no change.

Number (PK) Name State AppointedState Lines
1 Michael CO FL A
1 Michael CO FL H
2 George HI AZ A
2 George HI AZ H
3 Cindy AR AR H
3 Cindy AR CA A
3 Cindy AR CA H
3 Cindy AR IA A
3 Cindy AR IA H
3 Cindy AR ID A
3 Cindy AR KY A
3 Cindy AR OH A
4 Steven AR AZ A
4 Steven AR BA A
5 Jim TX CA A
5 Jim TX CA H

 

[blom0344]

This is similar to a slowly changing dimension. You should look into that (well documented in INFA help and documentation)
You basically perform lookups for the values Name State Appointed States and lines and then split the pipeline to perform an update/insert action

Ties Blom

 

[rasETL]

Aalph

What is the volume/file size you are processing?

Might it be easier to Truncate and Reload all data rather than try to maintain it?

 

[Aalph]

Hi RasETL / Blom,

The volume of data is very less. I am implementing the truncate and load method now. This is working very well.

Also, will be trying what Blom has suggested in the earlier post of composite key. (just curious to see how it can be implemented)

ThankYou !

 

[blom0344]

Be aware that truncate will effectively empty the table. If for some reason the session runs into trouble, then the truncate itself will have been performed. This may lead to empty reports etc. You may want to build some logic to capture such an event..

Ties Blom