×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

How to populate Foreign key column if it is not identity

How to populate Foreign key column if it is not identity

How to populate Foreign key column if it is not identity

(OP)
I have 1 flat file with 5 diff structures
1st row is header which is 1 for flat file
2nd is pharma record which is one for pharma
from 3 claim records under each pharmacy

i have imported them each time with diff structure

now i have to send them to 3 diff tables
header record to header table
claim to claim table
pharma to pharma table

each table is having PK which is identity
pharma is having PK aswell as FK (header PK)
claim is having PK as well as FK (pharma PK)


How can i populate this FK's in table

shall i use Seq T/R to populate these FK's

 

RE: How to populate Foreign key column if it is not identity

(OP)
1st table pk is FK in second table.
How can i populate that column
should i use seq generater for 1sttable.pk and 2nd table.FK
 

RE: How to populate Foreign key column if it is not identity

I don't think Informatica is real good processing a file with 3 different input sources. Regardless, I think it would be best to re-structure the file before processing.

It sounds to me as if there is a 1:1 between the header and pharma, then there are 3 claim records. So, if the order is...Header, Pharma, Claim, Claim, Claim, Header, Pharma, Claim, Claim, Claim, etc, you could do the following...

1.  Write header(s) to a temporary table with an additional column added - an Identity Column
2.  Write pharmas) to a temporary table with an additional column added - an Identity Column
3.  Write claim(s) to a temporary table with an additional column added - an Identity Column

4.  Then create a concatenated temporary table of the header and pharma where the Identity column is equal.

Then create a concatenated temporary table of the header/pharma combination from (4.) and the claim table where the identity column of the claim rec is...
- three times the Identity Id of the combo row
- three times + 1
- three times + 2

You should end up with One table which has all three records as a row. The header and pharma colums are duplicated for each of the three claim records. That Table can then be used to Update all three tables using Informatica.

Hope that makes sense, and it helps.    
 

RE: How to populate Foreign key column if it is not identity

Note - for below, the last table should match up the Identity column as follows...

- three times the Identity Id of the combo row
- three times - 1
- three times - 2

So, Identity Value 1  will use id 3, 2, 1
    Identity Value 15 will use id 45, 44, 43

Sorry for the confusion
 

RE: How to populate Foreign key column if it is not identity

(OP)
Thank you for the reply.

Any way i have completed the Task.
Actually my file is in the format
Header,pharm,claim,claim,claim,pharm,claim,claim,pharm,claim,claim.

1 header for every file, which will have batch no.
Pharm records 1 for each pharmacy which is also having batch no and pharm no.
claim records under each pharmacy.
claim records will have pahrm no which is used for identifying from which pharma it is.

so, i have 1 st loaded data into header table which contains PK

Then used header table as LKP, and used batchno in join condition
Header record and pharm record are having batch no

Then populated pharm table and used it as LKP table for claims mapping
In claims mapping join is on pharm no, pharm record and claim record is having pharmno.

As it is Fixed width file and there are different formats for each record i had lot of confussion at the beginning.

Any way managed to complete work successfully.
Thanks for u r reply Rasanders.



 

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