Table of Contents

Set up a new assignment

In this article, you will learn how to add a new assignment in the Aident SQL Migration app in Business Central.

Prerequisites

  • The SQL Migration Setup must be set up.
  • At least two databases with functioning database connections must be set up.
  • The permission set must be assigned to your user.

What's the purpose of an assignment?

In order to migrate data, a database assignment must be created. This database assignment determines from which source database to which destination database the data is transferred.

A database assignment can consist of several company assignments and table assignments. Table assignments, in turn, can consist of several field assignments and table part assignments. These determine which tables the data is read from and into which tables the corresponding data is written.

Assignments

By creating a company assignment, a migration can be carried out for several companies in the batch.

In a table assignment, a destination table is assigned a source table. It should be noted that only the original tables can be selected, but no extension tables (tables that contain fields from extensions). All fields of an extension can be defined in the field assignment. You can create table assignments individually manually or have them created automatically in batches for several tables.

Assigning a field from a source table to a field from a destination table is done within a table assignment. You can choose between all possible fields of the specified source table and the specified destination table. The fields of possible extensions are also displayed. These have the Source Is Extension or Destination Is Extension flag. In order to successfully migrate data, a correct primary key must be specified in the destination table during migration. The fields used for the primary key are marked with the Source Used for Primary Key or Destination Used for Primary Key flag.

Add Database Assignments

Follow these steps to add a new database assignment:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Create New Record
    • Click New in the action bar to create a new record. The Database Assignment Card page opens.
  3. Enter Database Assignment Code
    • Enter a Code for the database assignment in the General FastTab.
  4. Select Source and Destination Database
    • Enter a Source Database Code in the Settings FastTab to determine the source database.
    • Enter a Destination Database Code in the Settings FastTab to determine the destination database.

Add Company Assignments

Follow these steps to add a new company assignment:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Database Assignment
    • Open the database assignment for which a new table assignment is to be created. The Database Assignment Card page opens.
  3. Navigate to Company Assignments
    • Click Company Assignments in the Home action menu of the Database Assignment Card page. The Company Assignments page opens.
  4. Create Company Assignment
    • Enter a Destination Company Name and a corresponding Source Company Name in the Company Assignments page.
  5. Enable Company Assignment
    • Set the Enabled field to true activate the company assignment. Only enabled company assignments are taken into account when executing migrations in the batch. This has no effect when executing an SQL query from the table assignment.

Add Table Assignments Manually

Follow these steps to add a new table assignment manually:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a new table assignment is to be created. The Database Assignment Card page opens.
  3. Create Table Assignment
    • Enter the Source Table ID and the Destination Table ID in the Table Assignments SubPage of the Database Assignment Card page.
  4. Check Additional Settings
    • Assignment Type:
      • SQL Query: An SQL Query is created and executed for the table assignment.
      • No Migration: No SQL Query is created and executed.
    • Disable Indexes: If the flag is set, the indexes of the corresponding destination table can be (de)enabled (for migrating tables with many records).
    • Use Truncate Statement: Deletes the destination table before migrating with the TRUNCATE command (not recommended).
    • Ignore Where Statement: Ignores possible WHERE statements when generating SQL queries.
    • Keep Destination Data: Prevents the data in the destination table from being deleted before migration (necessary when migrating a table in multiple packages).

Add Table Assignments Automatically

Follow these steps to add a new table assignment automatically:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a new table assignment is to be created. The Database Assignment Card page opens.
  3. Create Table Assignments
    • Click Create Table Assignments in the Tables action menu. The request page Set Filters for Destination tables opens.
  4. Set Filter
    • Set a filter for e.g. Table ID to determine for which destination tables new table assignments should be created. If no filters are set, all possible destination tables will be traversed.
  5. Run Table Assignment
    • Click OK to start the automated creation of table assignments. For each destination table, a corresponding source table is searched for using the table ID. If no source table is found, no table assignment is created.
  6. Check Table Assignments
    • Check the created table assignments in the Table Assignments SubPage of the Database Assignment Card page.

Add Field Assignments Manually

Follow these steps to add a new field assignments manually:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a new field assignment is to be created. The Database Assignment Card page opens.
  3. Open Table Assignment
    • Open the table assignment for which a new field assignment is to be created. The Table Assignment Card page opens.
  4. Create Field Assignment
    • Enter the Source Field Name and a corresponding Destination Field Name in the Field Assignments SubPage of the Table Assignment Card page.
    • Set the Field Assignment Type to Migrate.

Add Field Assignments Automatically

Follow these steps to add new field assignments automatically:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a new field assignment is to be created. The Database Assignment Card page opens.
  3. Create Field Assignments
    • Click Create Field Assignments in the Fields action menu. The request page Filters for creating Field Assignments opens.
  4. Set Filter
    • Set a filter for e.g. Destination Table ID to determine for which table assignments new field assignments should be created. If no filters are set, all possible table assignments will be traversed.
  5. Run Field Assignment
    • Click OK to start the automated creation of field assignments. For each destination field, a corresponding source field is searched for using the field name.
    • Depending on whether a field assignment could be found, the following Field Assignment Types are set:
      • Check: there is an error in the field assignment, this can have various causes:
        • Differing or empty field names
        • Deviating field length (shorter destination field)
        • Different or incompatible data type
      • Migrate: a suitable field assignment is found, there are no errors and the value of the source field is migrated to the destination field
      • Initialize: there is a destination field to which no source field has been assigned, initial values are set
      • Skip: there is a source field to which no destination field has been assigned (no data transfer)
      • Expression: there is an expression that's taken into account when migrating the field
      • Data mapping: there is a data mapping that will be taken into account during the migration

Set Field Assignment Types Automatically

Follow these steps to add field assignment types automatically:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which a new field assignment is to be created. The Database Assignment Card page opens.
  3. Set Field Assignment Types
    • Click Set Field Assignment Types in the Fields action menu. The request page Filters to update the Field Assignment Type opens.
  4. Set Filter
    • Set a filter for e.g. Destination Table ID to determine for which table assignments the field assignment types should be set. If no filters are set, all table assignments are run through.
    • Click OK. The Field Assignment Type Selection page opens.
  5. Set Field Assignment Type Selection
    • Specify which field assignment type should be set as default.
  6. Run Set Field Assignment Type
    • Click OK to start the automatic setting of the field assignment type.

Check Field Assignments

Follow these steps to check field assignments:

  1. Navigate to Database Assignments
    • In Business Central, search for and go to the Database Assignments page.
  2. Open Database Assignment
    • Open the database assignment for which the field assignments is to be checked. The Database Assignment Card page opens.
  3. Check Field Assignments
    • Click Check Field Assignments in the Fields action menu. The request page Filters to Check the Field Assignments opens.
  4. Set Filter
    • Set a filter for e.g. Destination Table ID to determine for which table assignments the field assignments should be checked. If no filters are set, all table assignments are run through.
  5. Set Field Assignment Type Selection
    • Specify which field assignment type should be set as default.
  6. Run Check Field Assignments
    • Click OK to start the check of the field assignments.
  7. Check Field Assignment Status
    • Check the Field Assignment Status for the table assignments in the Table Assignment List SubPage of the Database Assignment Card page. The status must be OK to migrate data successfully.

Next step

Create and run SQL queries