Hi guys,
I have a job that determines the inserts. our target table generates the id on the database level when an insert command is issued to it. Once my inserts are successful and the ids are generated, i have to get that id and use that id to update the mapping table. The problem is that my target table has only one key that the database generates.
what would be the best approach to get that key.
anyone faced such a scenario before.
Thanks in advance guys
getting identity columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Your target table has the surrogate key and the fields that comprise the natural key, even if they are not defined as key fields. You update your mapping table by retrieving the surrogate and natural key fields from your target table using some type of incremental load filter such as processing id or timestamp or start date and load those into your mapping table. You could also dispose of and recreate your mapping table entirely. They are usually stored in an ETL optimised format such as a hash file or dataset.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn