Hi, I'm pretty sure there isn't built-in support as the keys are determined on the server, not in rhino.etl, and if it's a bulk upload then there isn't an easy way to get them short of re-querying.
The easiest way of doing this that I've found if you know the keys are unique in the input data and you can set identity_insert on in the database, is just use (starting_point - original_keyvalue) as the key instead, choosing starting_point yourself to avoid overlaps with any other data you've imported. If you keep track of the keys yourself, you end up with lots of "lookups" which make the pipelines look more convoluted than they need to be. In this case, where I have lots of lookups but not much else, I tend to create staging tables into which I load the cleaned + transformed data, then use SQL to match the new primary keys and move the data into the production tables. Otherwise, you're keeping track of them yourself. In my opinion, it's easier if you can assign the keys yourself (and have then use the output of the "person" pipeline as input to a join in the "person_phone" pipeline) rather than having the database do them, as this reduces the number of steps. If your input files are sorted in the same order, then less data will be held in memory during the pipeline. Hope that helps, Miles On Wed, Oct 13, 2010 at 10:09 PM, Seth Petry-Johnson <[email protected] > wrote: > I'm evaluating Rhino ETL and I understand the basics, but I'm confused > as to how to handle foreign key relationships when loading data. > > For example, my source DB has "person" and "person_phone" tables, > where each person can have 0 or more phone number records. In my > destination DB, PERSON has an autoincrementing PK. How do I structure > my Process and Operations such that I can link child Phone rows to the > parent Person? > > Is this built into the ETL library, or do I need to manually keep > track of these IDs? > > Thanks! > > Seth > > -- > You received this message because you are subscribed to the Google Groups > "Rhino Tools Dev" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<rhino-tools-dev%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/rhino-tools-dev?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Rhino Tools Dev" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rhino-tools-dev?hl=en.
