Training:Import data from other SQL servers

Import data from other SQL servers

This page was created by Alexandra on 2018-10-21. Last edited by Wikiadmin on 2026-07-29.

You can use SQLImport in a MDrivenServer ServerSide ViewModel to read rows from an external SQL-based database and create or update objects in your MDriven model; this is for developers who need scheduled, model-driven imports without an external import program.

What SQLImport does

SQLImport runs a SQL query against an external database, receives the result set, and maps each returned row to columns in a connected import nesting. MDrivenServer then creates or updates the corresponding model objects through that ViewModel.

For example, an external database can provide reference data for Class1. A ServerSide ViewModel queries the external database, and its import nesting receives columns such as Code, Name, and other attributes of Class1.

Use this pattern when MDrivenServer must periodically collect data from another SQL-based system. For file-based exports, see Training:SQLExport from MDriven Server. For tab-separated data imported through the debugger, see Documentation:Import Data.

Before you start

Define the target class and attributes in MDriven Designer. In this example, Class1 is the class that receives external reference data.
Create a ServerSide ViewModel that can run on MDrivenServer.
Ensure that the account and environment in which MDrivenServer runs can reach the external database.
Write a query whose result columns match the columns exposed by the import nesting.

Configure the import ViewModel

Create one ServerSide ViewModel that holds the external connection details, the query, and the import action. The current design uses a connected nesting in this ViewModel to describe each imported row.

Item	Purpose	Example
`connectionstring`	The connection string for the external SQL Server database.	A value that identifies the external database to query.
`connectionstringodbc`	An alternative connection-string column. When used, SQLImport uses ODBC instead of the SQL Server connection mechanism.	Use this when the external source must be accessed through ODBC.
`Query`	The SQL query that returns the rows to import. You can build the query with data from the rest of the ViewModel.	A query returning a code, name, and other values for `Class1`.
`Key`	Names the target-class attribute used to find an existing object for update.	`Code`, when `Class1.Code` identifies a row.
`CommandTimeout`	Optional integer timeout in seconds for the external SQL command. The default is 30 seconds.	`120` for a query that is expected to take up to two minutes.
Connected import nesting	Defines the columns that receive one row from the SQL result set.	A nesting with editable columns `Code`, `Name`, and `Description` for `Class1`.
`SQLImport` action	The action name that triggers the MDrivenServer import function.	An action named exactly `SQLImport`.

Define the import nesting

The connected nesting is the import template. Its columns define how the SQL result set is received and mapped to your model.

Add a connected nesting to the ViewModel that contains the SQLImport action.
Set up the nesting so that it exposes the target attributes. For Class1, this can be Code, Name, Value1, and Value2.
Make the nesting columns editable. Columns are created as read-only by default; read-only columns do not receive imported values.
Make the SQL query return columns in the same order as the import nesting expects.

For example, if the nesting begins with Class1.Code, the first column returned by the query must be the external code. The remaining result columns must correspond to the remaining import columns.

Configure updates with Key

Set Key when repeated imports must update existing objects rather than create duplicates. The value of Key is the name of an attribute on the target class, not the key value itself.

The named attribute must meet both requirements:

It is an attribute on the class being imported, such as Class1.Code.
It is represented by the first column of the import nesting and therefore by the first column returned from SQL.

For example, set Key to Code and return the external code as the first query column. SQLImport can then use Class1.Code to update the matching Class1 object.

Add actions

Add an action named exactly SQLImport. This name activates the MDrivenServer SQLImport behavior.

You can add actions that run after the import. For example, a Finished action can set Class2.Attribute1 to 'Done'. This lets a scheduled job mark its triggering object as handled after importing data.

Run the import on a schedule

Use a MDrivenServer ServerSide job to run the import when work is available. ServerSide jobs evaluate an expression periodically, fetch matching objects, and execute the actions in the ServerSide ViewModel. See Training:MDrivenServer periodic server-side actions for the general scheduling pattern.

The following example uses Class2 as a work item:

Create a ServerSide job for the ViewModel that contains the SQLImport action.
Configure the job to evaluate the following expression every 20 seconds:

Class2.allinstances->select(attribute1='todo')

Configure the job to fetch at most two matching objects on each run.
Let the import complete, then run the Finished action to set Class2.Attribute1 to 'Done'.

When no Class2 object has Attribute1 = 'todo', the job finds no work. When you create and save a Class2 object with that value, the next job run imports the data and marks the object as done.

Migrate an older two-ViewModel design

Older SQLImport designs use two ViewModels:

The main ViewModel has a column of type ViewModel / new Nesting whose value names another ViewModel.
The second ViewModel defines the columns that receive each SQL result row.

Use the newer compact design instead:

Keep the connection, query, key, timeout, and SQLImport action in the main ViewModel.
Replace the column that points to the separate import ViewModel with a connected nesting in the main ViewModel.
Define the import columns in that nesting.
Verify that every import column is editable and that the first nesting column matches the attribute named by Key.

The nesting now provides the import-row definition directly, so a separate importer ViewModel is no longer required.

Import related tables

SQLImport can import individual tables through their respective import templates. If the imported data includes associations between classes, choose the association strategy based on the amount of data:

For a limited value set, use a Combobox for a single link in the import grid.
For large related tables, import enough key values as attributes first, then connect the objects in a later ServerSide job or with sqlpassthrough.

For the detailed association pattern and example SQL, see Documentation:SQLImport multiple tables with associations.

Troubleshooting

Symptom	Check
Imported objects are created repeatedly instead of updated.	Set `Key` to the name of the target-class key attribute, and return that attribute as the first column in both the nesting and the SQL result.
Imported columns have no values.	Check that the corresponding columns in the import nesting are not read-only.
The external query times out.	Add `CommandTimeout` with an integer number of seconds. Without it, the default is 30 seconds.
The job keeps processing the same work item.	Add a completion action, such as setting the work item's state from `'todo'` to `'Done'`, so that it no longer matches the job expression.
Related objects are not connected after import.	Import the necessary key attributes and apply the related-table strategy in Documentation:SQLImport multiple tables with associations.

Contents