Setting a Default for a New Column

juliajuliabelljuliajuliabell Posts: 10
edited June 18, 2014 8:04AM in SQL Compare Previous Versions
Is it possible to set a default value for a newly created NOT NULL column, without actually setting a default value in the database?

I guess in order to do this the script would have to create the column as NULL, update it with the default value and then alter it to NOT NULL. Or something along those lines.

Comments

  • Is it possible to set a default value for a newly created NOT NULL column, without actually setting a default value in the database?

    I guess in order to do this the script would have to create the column as NULL, update it with the default value and then alter it to NOT NULL. Or something along those lines.

    This isn't possible right now, although it's something we're hoping to add soon. Can I ask whether you'd prefer to specify your own default, or if your source database has actual data values from which the values going into the new column can be populated?

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • For me, just an option to specify a single value when the column is being created would be sufficient. There are scheduled routines in the database that manage the value of the column I am adding, so the value of the column at creation doesn't really matter. It just needs to have a value so I can define the column as NOT NULL.

    Julia.
  • Thanks for that. This is a feature that is close to the top of our priority list so do look out for it in the next major version. In the meantime, the easiest thing to do is to change the columns to take a default, then change them back afterwards.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Couldn't you just put the value you wanted as the default value for the column, run the synch, remove the default value, and then resynch?
  • Hi jheidt

    Thanks for the reply. I could do that, but I was looking for a solution that was more automated. The more manual steps there are, the more chance there is of introducing an error into the process.
  • I would love this feature as well. I have a suite of tables generated by script that I need to upgrade, being able to do this in RedGate would be much preferred to editting the tables manually.

    Specifically, there's two ways I would like to see this implemented:

    - Add a default to the temp tables created when copying the data.
    - Add a default value to the SELECT used to copy data to the temp table (e.g. INSERT INTO [tmp_rg_XXX] ([col1],[col2],[col3]) SELECT [col1], [col2], 0 FROM [OldTable] ...)

    That is, I don't think a default should be added to the actual tables. If you want that, then I agree that you should just add the default to the new or old table before doing the compare.
  • I could really use this feature as well.

    I'm making changes to an existing software package. When I start a fix or feature upgrade process, I SQL Data Compare the data into my Dev database, make my changes, including new fields, with defaults, and some NOT NULL.

    Because I then Sync back to the existing Prod db when it's ready, I start with SQL Compare, then do the Data Compare second. SQL Compare tries to create the new field as NOT NULL which it can't do with some data.

    I would prefer that the default is pulled from the schema, in case it's a formula, but for me it doesn't really matter since I'm most likely going to do a Data Compare right afterwords.
  • SQL Compare 9.5 can re-use custom migration scripts that have been committed to SQL Source Control 3. This means you can modify your scripts to update the existing values without having to set a default.

    http://www.red-gate.com/MessageBoard/vi ... 1312#51312

    Let us know if this satisfies your needs.

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • This does not meet the need of users who use SQL Compare but not SQL Source Control. We use SSDT projects and TFS for source control but migration of designs from environment to environment (ex. Stage to Production) are performed using SQL Compare.

    Would you consider adding this to SQL Compare.
  • Hi Richard,

    We are currently researching ways to extend migrations to SSDT projects, allowing users to use this functionality without using Source Control, and I would be really interested learning more about your requirements for a solution. Do you think you would be available for a brief research call (ca. 30 minutes)? If you are, would you mind dropping me a line at daniel.rothig [at] red-gate.com for more details?

    Many thanks

    Daniel Rothig
    Product Manager
    Red Gate Software
Sign In or Register to comment.