Options
Cannot insert the value NULL into column
dbirdil
Posts: 8
I am having a problem adding new columns to a table. The new table definition has new columns that are both NULL and NOT NULL.
The SQL Compare Script creates a temp table to contain data from the target table. The insert it uses to populate this temp table fails because it does not provide a value for the new columns defined as NOT NULL.
I get the message:
[515] Cannot insert the value NULL into column 'escrow', table
How can I get SQL Compare to generate a script that will work when adding columns defined as NOT NULL?
The SQL Compare Script creates a temp table to contain data from the target table. The insert it uses to populate this temp table fails because it does not provide a value for the new columns defined as NOT NULL.
I get the message:
[515] Cannot insert the value NULL into column 'escrow', table
How can I get SQL Compare to generate a script that will work when adding columns defined as NOT NULL?
This discussion has been closed.
Comments
If the columns you're adding don't allow nulls, then the only workaround is to add a default value to the source column, that way all of the values in the new column will be filled in with the appropriate specified value rather than SQL Server's default action, which is to insert a NULL into every row of the new column.
The work around for me would be to specify a default value on the INSERT. I would use the system defaults like spoce, 0 or current_timestamp.
I am looking for a tool that will automate this as much as possible.
I have been using Embarcadero's Change Manager. But their product is slow and they do not offer a data compare tool so that is why I am looking at your products. It does handle columns defined as NOT NULL without a default properly
Yes, that would be a good idea. Especially in the case where you change a column from null to not null. Even adding a column default doesn't fix that.
[We have emailed you with a solution (DA)]