×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Primary Key with Multiple Values

Primary Key with Multiple Values

Primary Key with Multiple Values

(OP)
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.

RE: Primary Key with Multiple Values

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
 
 

RE: Primary Key with Multiple Values

(OP)
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

 

RE: Primary Key with Multiple Values

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
 
 

RE: Primary Key with Multiple Values

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?

RE: Primary Key with Multiple Values

(OP)
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 !

RE: Primary Key with Multiple Values

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
 
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close