SQL Upgrade Script Generator
This is a partner only tool.
Purpose
When we upgrade customers from NAV to BC, we need to refactor customisations in C/AL to AL extensions. The AL extensions have a schema different from C/AL customisations. Based on our experience, the fastest way to transfer custom fields and entities is using an SQL script while still on-prem. At a high level, the process is:
- Upgrade NAV to at least NAV 2015 (schema only)
- Make a copy of the NAV 2015 database - you will use this copy for the upgrade process.
- Upgrade to the latest version of Business Central following the Microsoft guidelines without installing your custom extensions. You must also follow guidelines for upgrading ISV solutions.
- Once the standard Business Central and ISV solution tables and fields are upgraded, use the script generated by this process to transfer customer entities and fields.
- After the script is executed you can now migrate the on-prem data to the cloud.
Using the Generator
This page allows you to create an SQL script to transfer custom data from NAV to BC extension: You can run it from the Run Object page by selecting the Generate SQL Script action.
Before running this page, you should prepare the custom field list from the NAV environment using the Field table and copy this list to the Excel file.
For custom fields :
For custom tables :
Then, you should merge these lists in an Excel file :
The last step is to copy all the data (except the header row if it exists) from the Excel file and paste it to the Generate SQL Script page. You can either copy from Excel and paste in here or use a configuration package.
Page Actions
| Action | Description |
|---|---|
| Automatic Mapping | This action fills the destination table/field information using the field number, name, or caption. You must install the destination extension in the environment before running this action. You can also map the fields manually if needed. |
| Empty Destination Information | Clears all the destination information from the page. |
| Generate SQL Script | Generates the SQL script files according to your mapping. This action skips the records that have missing destination information. |
Page Fields
| Field | Description |
|---|---|
| Has Auto-Increment Key Field | If the custom table has any auto-increment field, this Field should be marked. This Field aims to set the identity insert for the related table before the insert script. It's enough only to mark a field per table. |
| Exclude Field | By marking this value, You can skip generating the SQL script for related fields. |
After completing the setup, you should run the Generate SQL Script action. This action will open the request page of the report :
| Option | Description |
|---|---|
| App ID | BC extension App Id |
| Source Database | NAV database name |
| Source Company Filter | The company filter the app will use in the SQL script. Leave it empty if you want to include all companies. |
| Destination Database | BC database name |
This report will generate two separate SQL files: one is for custom fields, and another is for custom tables. You can directly run these scripts from SQL Management Studio to transfer the data from NAV to BC extension.