...
In order to properly convert the data and guarantee consistency and integrity across systems, we need to ensure the relationships are being respected through the entire migration process. To accomplish achieve a consistent robust process that is scalable and repeatable across all projects, we will be employing standard staging tables.
While there are many approaches that can be applied to the staging tables we will be implementing, I will be breaking down the most common approach that we have already put into place for some of the Projects in our organization. In the future we can continue to evolve it into more complex versions as demand arises.
...
i.e., The rows in PTG_ImportStaging_tblParty.PrimaryPartyType have a properly configured and matching value in the corresponding PrimaryPartyType Lookup Group. The assigned engineers can then easily check the exceptions and work through the reported exceptions with ease.
Inserting to destination tables
Once the data is ready to be loaded, we can make the use of a MERGE INTO to be able to insert rows as we copy back the generated ID’s to the staging tables through an OUTPUT clause.
...