Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Primary Key with Multiple Values 1

Status
Not open for further replies.

Aalph

Technical User
May 19, 2009
8
US
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.
 
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

 
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

 
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

 
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?
 
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 !
 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top