You can import related SQL tables with SQLImport and connect the imported objects through a model association; this page is for developers importing parent and child data from an external SQL database.

Choose an association strategy

An association, also called a link, connects two classes and therefore their objects and database tables. Import the objects in each table first, then establish the association between them.

Situation	Recommended approach	Why
The related class has a limited number of objects	Set the single-link association in the import grid with a combobox.	You can select the related object directly while importing each row.
Both imported tables contain many rows	Import a matching external key into attributes on both classes, then resolve the association in a separate server-side step.	A combobox containing a large table is impractical. The matching key lets a database update connect all matching rows in one operation.

For the SQLImport ViewModel structure, external connection configuration, import key requirements, and server-side scheduling, see Training:Import data from other SQL servers.

Import a small lookup table with a combobox

Use this approach when the objects at the single-link end are few enough to select in the import grid.

For example, if imported SAPPlantMaterial rows each belong to one of a small number of already imported profit centers:

1. Create the association between SAPPlantMaterial and SAPProfitCenter in the model.
2. Configure the SQLImport row definition so that the material row includes the single-link association.
3. Use a combobox for that single link and select the corresponding SAPProfitCenter object for each imported row.
4. Run the import and verify that a material object follows its association to the expected profit center.

This method writes the relationship as part of the row import. Do not use it when the lookup list is large; use matching keys instead.

Resolve large imports by matching keys

When both tables have many rows, retain enough source-system key data to match one imported class to the other. These are business keys used for matching, not the MDriven database identifiers that implement the association.

In the example below:

• SAPProfitCenter.ProfitCenter is the imported profit-center business key.
• SAPPlantMaterial.ProfitCenterKey is the imported material-row value that identifies its profit center.
• SAPProfitCenterID is the association foreign-key column on SAPPlantMaterial.

Procedure

1. Model the association between the two classes. For an ordinary parent/child relationship, use an association; use an association class only when the relationship itself needs data, lifetime control, or uniqueness semantics.
2. Add importable attributes for the external keys needed to match the rows. In this example, import ProfitCenter on SAPProfitCenter and ProfitCenterKey on SAPPlantMaterial.
3. Import the referenced objects first. Here, import SAPProfitCenter before SAPPlantMaterial so each matching profit center exists when links are resolved.
4. Import the dependent objects and their matching key values. At this point, materials may exist without their SAPProfitCenter association set.
5. Run a separate server-side action that executes an SQLPassthrough update to set the association foreign key from the matching row.
6. Check rows whose source key did not match. They will not receive an association from the update and require source-data correction or separate handling.

SQLPassthrough example

The following SQLPassthrough expression updates all material rows whose imported ProfitCenterKey equals a profit center's imported ProfitCenter value:

SAPPlantMaterial.sqlpassthrough( 'update SAPPlantMaterial set SAPProfitCenterID=SAPProfitCenter.SAPProfitCenterID   from SAPPlantMaterial join SAPProfitCenter on  SAPProfitCenter.ProfitCenter=SAPPlantMaterial.ProfitCenterKey',  Integer)

The SQL performs these steps:

1. It joins SAPPlantMaterial to SAPProfitCenter where SAPProfitCenter.ProfitCenter equals SAPPlantMaterial.ProfitCenterKey.
2. For every matched material row, it sets SAPPlantMaterial.SAPProfitCenterID to the matching SAPProfitCenter.SAPProfitCenterID.
3. The foreign-key value represents the modeled association, so the material object is linked to that profit-center object.

Run the association-resolution action only after both imports have completed. If the import runs periodically, schedule the resolution step after the two import steps in the same server-side workflow. Training:Import data from other SQL servers describes using a server-side job to run import actions.

Validate the result

After the update, validate both the model relationship and the data quality:

• Open or inspect an imported material and confirm that its single link leads to the expected profit center.
• Identify material rows with a ProfitCenterKey that has no corresponding SAPProfitCenter.ProfitCenter. Those rows remain unlinked.
• Check that the source key identifies one intended target. If more than one profit-center row uses the same ProfitCenter value, the source data does not provide an unambiguous match.
• Re-run the matching step after importing new or corrected profit centers and materials.

Keep matching data intentional

The imported key attributes are integration data: they make the relationship resolution explicit and repeatable. Keep them when later imports need to find the same related object. If an attribute is intended to be calculated rather than imported and stored, see Derived attributes & associations and Derived settable attributes.

Use SQLPassthrough only for the set-based relationship update described here. For general SQL concepts and syntax guidance, see Documentation:SQL.

See also

• Training:Import data from other SQL servers
• Documentation:Association
• Training:Association classes
• Documentation:SQL
• HowTos:Reverse Engineer a Database