Dynamics Community Forum Thread Details

Migrating Orphan Tables to D365 F&SCM AOT — Sync Failures & Data Restoration Issue

Hi all,

We are migrating legacy tables that exist in AXDB but were never created through the AOT. We created AOT definitions for these tables and triggered sync — but sync kept failing because the table already existed in SQL.

Temporary Solution

We renamed the legacy table so D365 could create it fresh:
sql

EXEC sp_rename 'SFD10001', 'SFD10001_OLD'

Sync then successfully created a clean D365-managed SFD10001.

The New Problem — Data Restoration

When we tried restoring data from the old table back into the new one:
sql

INSERT INTO SFD10001 (...) SELECT (...) FROM SFD10001_OLD

It failed because the old table had nullable values in several columns, but D365 creates all columns as NOT NULL — even when Mandatory = No is set in AOT. D365 does not support nullable columns at all; instead it substitutes default values (0, '', 1900-01-01) for empty fields.

So any NULL values in the old table have no direct equivalent in the new D365-managed table.

Has anyone dealt with this data restoration challenge? How did you handle NULL values when migrating legacy data into AOT-managed tables?

Categories:

Dynamics 365 Supply Chain Management

Yes. This is expected behavior in Dynamics 365 F&SCM. AOT-managed tables do not support SQL NULL values. Regardless of whether Mandatory is set to Yes or No, the physical SQL columns are created as NOT NULL. The Mandatory property is enforced by the application, not by allowing SQL NULLs.

Your options are:

Transform the data during migration by replacing NULL values with appropriate defaults using ISNULL() or COALESCE(). For example, map NULL strings to '', integers to 0, dates to the appropriate default (or another business-appropriate value), and enums to a valid enum value.

Review each nullable column individually to determine whether a default value preserves the intended business meaning. A blanket replacement isn't always appropriate.

If the distinction between NULL and a default value is business-critical, you'll need to redesign the table (for example, add a flag indicating whether a value was originally unknown), since D365 F&SCM cannot persist SQL NULLs in AOT-managed tables.

This data transformation step is a normal part of migrating legacy SQL tables into AOT-managed tables. There is no supported way to make AOT-generated columns nullable in the database.

Quick Links

Leaderboard > Supply chain | Supply Chain Management, Commerce