Table of Contents

Table Part Assignments

In this article, you will learn how to use table part assignments in the Aident SQL Migration app in Business Central.

What's the purpose of the function?

Sometimes it is necessary to migrate data from a table in several data packages, e.g. because a table contains a particularly large number of data records or because only a certain range of data records should be migrated before commissioning. This is to be made possible by the use of table part assignments. These can be narrowed down or selected by setting filter values to primary key fields.

For example, data from an entry table can be migrated for a specific number range by entering the Entry No. as a filter. When creating a table part assignment, the settings of a table assignment and the associated field assignments are inherited, i.e. all table part assignments have the same settings. In addition, separate SQL queries are created for all table part assignments to delete and migrate the data.

Once table part assignments are set up, only those parts are executed, taking into account the filters created.

It should be noted that when using table part assignments, the data of the destination table should only be deleted when the first part is executed. If the deletion of the destination data is also carried out before further table part assignments are executed, data that has already been migrated from previous partial migrations no longer exists in the destination table. Failure to delete the destination table before migrating the first part can result in primary key violations and the affected records won't be migrated.

Create a new table part assignment

Follow these steps to create a new table part assignment:

  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 table part assignment should be created. The Database Assignment Card page opens.
  3. Open Table Assignment
    • Open the table assignment for which the table part assignment should be created. The Table Assignment Card page opens.
  4. Create Table Part Assignment Line
    • Select a primary key field to filter in Source Table Filter 1 Field in the Table Part Assignments SubPage.
    • Enter a filter value in the Source Table Filter 1 From and Source Table Filter 1 To fields. If no value is specified in the Source Table Filter 1 To field, the selected primary key field is filtered only with the value from the Source Table 1 From field.
    • If you want, you can select an additional primary key field to filter in Source Table Filter 2 Field and an additional filter value in the Source Table Filter 2 From and Source Table Filter 2 To fields. If no value is specified in the Source Table Filter 2 To field, the selected primary key field is filtered only with the value from the Source Table 2 From field.
    • For a further table part assignment, create another line with the necessary filters.
    • When using the filter fields, please note that there is no overlap between the filter areas, otherwise primary key violations could occur during the migration and the migration for the affected table could be aborted.
  5. Enable Table Part Assignment Lines
    • Set Enabled to true for the table part assignment lines. This is to prevent all table part assignments from being executed accidentally.
  6. Set Keep Destination Data
    • Set Keep Destination Data to false to run the deletion query before migration or set Keep Destination Data to true if the destination data should not be deleted before migration. By default, when you run a migration, a SQL query is executed to delete the data in the destination table. However, when using table part assignments, this would lead to the fact that already migrated data from a previously executed table assignment would be deleted again.
    • To prevent the deletion of previously migrated data in the destination table, check the Keep Destination Data field for all subsequent table part assignments.
  7. Set Source Table Condition
    • Enter a SQL expression in Source Table Condition or Source Table Condition 2 to add additional filter criteria.
    • Click the AssistEdit button to display an overview of possible SQL expressions and associated examples. The SQL Expressions page opens.
    • Select an example and click OK to apply it. The expression is adopted and can then be adjusted.
  8. Create and execute query for table part assignment
    • Click Create Query, Delete Destination Data or Transfer Data in the action bar to create (and execute) the query for a single table part assignment.

Delete existing table part assignments

Follow these steps to delete table part 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 table part assignment should be created. The Database Assignment Card page opens.
  3. Open Table Assignment
    • Open the table assignment for which the table part assignment should be created. The Table Assignment Card page opens.
  4. Delete Table Part Assignment Line
    • Select the last line in the Table Part Assignments SubPage and delete it.
    • Table part assignments can only be deleted in the bottom-up principle. That is, the part with the highest number must be deleted first before the other parts can be deleted.

Next step

SQL Expressions