“Cannot insert the value NULL into column ‘ColumnName’, table ‘TableName; column does not allow nulls. INSERT fails.” error

Applies to
ApexSQL Data Diff

Symptoms
The “Cannot insert the value NULL into column ‘ColumnName’, table ‘TableName; column does not allow nulls. INSERT fails.” error is encountered when running ApexSQL Data Diff synchronization script.

Workaround
This problem is resolved in the following way.

First, a table that is mentioned in the error report need its structure to be checked to see if the columns are set to allow NULL, since it wouldn’t be possible to insert NULL value into a cell where it is not allowed. An example of a table structure can be seen in the image below.

If NULL values are enabled, then a column that is mentioned in the error report must be checked if it is using a BLOB data type (image, text, ntext, varbinary(max), varchar(max), nvarchar(max), or XML).

If the column mentioned in the error report is using one of these data types then:

  1. In the Options tab of the New project window, check if the Ignore BLOB columns option is checked

    Note: The Ignore BLOB columns option is checked by default to improve performance during the comparison process.

  2. Uncheck the Ignore BLOB columns option.

Affected versions
ApexSQL Data Diff     All