Table of Contents

Table Assignment Parts

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

What's the purpose of the function?

Sometimes it's necessary to migrate data from a table in multiple data packages, for example, because a table contains a particularly large number of records or because only a specific range of records needs to be migrated before deployment. This can be achieved by using table assignment parts. These can be restricted or selected by setting filter values ​​for primary key fields.

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

Table assignment parts can be created manually or automatically. Automatic creation of the parts takes into account the number of records in the source table per company and the maximum number of records per migration query. The parts are then generated with the upper and lower limits for each configured destination company.

Once the table assignment parts are set up, only these parts are executed taking into account the created filters.

It should be noted that when using table assignment parts, the data in the destination table should only be deleted when the first part is executed. If the destination data is deleted before further table assignment parts, any data already migrated from previous partial migrations will no longer exist in the destination table. Failure to delete the destination table data before migrating the first part can lead to primary key violations, and the affected records won't be migrated.

Set maximum number of records per migration query

Follow these steps to set the maximum number of records per migration query:

  1. Navigate to SQL Migration Setup
    • In Business Central, search for and go to the SQL Migration Setup page.
  2. Set maximum number of records
    • Open the Default Packages FastTab and enter the number of records to be migrated per migration query in the Default No. of Records per Query field. This value should be between 1 million and 10 million records.

Automatic creation of table assignment parts

Follow these steps to automatically create table assignment parts:

  1. Navigate to Database Assignments
  • In Business Central, search for and open the Database Assignments page.
  1. Open Database Assignment
  • Open the database assignment for which you want to create a table assignment part. The Database Assignment Card page opens.
  1. Set the maximum number of records per migration query
  • In the Max. No. of Records per Query field, enter the number of records to be migrated per migration query. This value should be between 1 million and 10 million records.
  1. Navigate to Table Assignment Parts
  • In the Table Assignment Parts action menu on the Database Assignment Card page, click Table Assignment Parts. The Table Assignment Parts page opens and displays all existing table assignment part headers.
  1. Get Possible Table Assignments
  • Click Get Possible Assignments in the actions menu. The previously created company and table assignments are run through, and the records of the corresponding source tables are counted for each destination company. If the number of retrieved records is greater than the number of records per migration query, a new table assignment part header is created for the destination company and the table assignment. Review the created table assignment part headers.
  1. Create Parts
  • Select the records for which the parts should be created automatically and click Create Parts in the actions menu. An SQL query is now executed that creates the parts, including the upper and lower limits.

Create a Table Assignment Part Manually

Follow these steps to create table assignment parts manually:

  1. Navigate to Database Assignments
  • In Business Central, search for and open the Database Assignments page.
  1. Open Database Assignment
  • Open the database assignment for which you want to create a table assignment part. The Database Assignment Card page opens.
  1. Set the maximum number of records per migration query
  • In the Max. Number of Records per Query field, enter the number of records to be migrated per migration query. This value should be between 1 million and 10 million records.
  1. Navigate to Table Assignment Parts
  • In the Table Assignment Parts action menu on the Database Assignment Card page, click Table Assignment Parts. The Table Assignment Parts page opens and displays all existing table assignment part headers.
  1. Create a new table assignment part header
  • Click New in the actions menu. The Table Assignment Part Card page opens.
  • In the General FastTab, select a Destination Company and a Destination Table ID. The corresponding Source Company is automatically retrieved from the company assignment. The corresponding Source Table is automatically retrieved from the table assignment.
  1. Create Parts
  • Create a new line on the Table Assignment Part Lines subpage.
  • Select a primary key field in Source Table Filter Field 1 for filtering.
  • 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 will be filtered only with the value from the Source Table Filter 1 From field.
  • If you want, you can select an additional primary key field in the Source Table Filter Field 2 and enter 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 will be filtered only with the value from the Source Table Filter 2 From field.
  • For an additional table assignment part, create a new line with the necessary filters.
  • When using the filter fields, ensure that there are no overlaps between the filter ranges, as otherwise primary key violations could occur during migration and the migration could be aborted for the affected table.
  1. Enable Table Assignment Parts
  • Set Enabled to true for the table assignment part line. This prevents all table assignment parts from being accidentally executed.
  1. Set Keep Destination Data
  • Set Keep Destination Data to false to execute the delete query before migration, or set Keep Destination Data to true if you don't want the destination data to be deleted before migration. By default, a migration executes an SQL query to delete the data in the destination table. However, using table assignment parts would result in previously migrated data from a previously executed table assignment being deleted again.
  • To prevent previously migrated data from the destination table from being deleted, check the Keep Destination Data field for all subsequent table assignment parts.
  1. Set Source Table Condition
  • Enter an SQL expression in Source Table Condition or Source Table Condition 2 to add additional filter criteria.
  • Click the AssistEdit button for an overview of possible SQL expressions and corresponding examples. The SQL Expressions page opens.
  • Select an example and click OK to apply it. The expression is applied and can then be customized.
  1. Create and execute a query for the table assignment part
  • Click Create Query, Delete Target Data, or Transfer Data in the action bar to create (and execute) the query for an individual table assignment part.

Delete existing table assignment parts

Follow these steps to delete table assignment parts:

  1. Navigate to Database Assignment
  • In Business Central, search for and open the Database Assignment page.
  1. Open Database Assignment
  • Open the database assignment for which you want to delete a table assignment part. The Database Assignment Card page opens.
  1. Navigate to Table Assignment Parts
  • Click Table Assignment Parts in the Table Assignment Parts action menu on the Database Assignment Card page. The Table Assignment Parts page opens and displays all existing table assignment part headers.
  1. Open Table Assignment Part
  • Open the table assignment part for which you want to delete a table assignment part line. The Table Assignment Part Card page opens.
  1. Delete Table Assignment Part Row
  • Select the last line on the Table Assignment Part Lines subpage and delete it.
  • Table assignment parts can only be deleted from bottom to top. This means that the part with the highest number must be deleted first before the other parts can be deleted.

Next step

SQL Expressions