What are the challenges you face when working across database platforms? Take the survey
Options

Cannot insert the value NULL into column

dbirdildbirdil Posts: 8
edited September 14, 2006 12:36AM in SQL Compare Previous Versions
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?

Comments

  • Options
    Hi,

    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.
  • Options
    Unfortunately the column definition must remain NOT NULL without a default.

    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
  • Options
    Hi,

    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)]
This discussion has been closed.