Skip to main content

SQL Upgrade Script Generator

note

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. Generate SQL Script Action

info

Depending on the version target Business Central database, there could be a different schema. You can choose to either create for versions < = BC22 or > = BC23.

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 :

Custom Fields

For custom tables :

Custom Tables

Then, you should merge these lists in an Excel file :

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

Page Actions

ActionDescription
Automatic MappingThis 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 InformationClears all the destination information from the page.
Generate SQL ScriptGenerates the SQL script files according to your mapping. This action skips the records that have missing destination information.

Page Fields

Page fields

FieldDescription
Has Auto-Increment Key FieldIf 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 FieldBy 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 : Generate SQL Script

OptionDescription
App IDBC extension App Id
Source DatabaseNAV database name
Source Company FilterThe company filter the app will use in the SQL script. Leave it empty if you want to include all companies.
Destination DatabaseBC 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.