Setting a Default for a New Column

juliajuliabell · January 2, 2010 7:45AM

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.

David Atkinson · January 3, 2010 1:57PM

juliajuliabell wrote:

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

juliajuliabell · January 4, 2010 4:36AM

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.

David Atkinson · January 4, 2010 4:56AM

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

jheidt · January 14, 2010 9:31AM

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?

juliajuliabell · January 14, 2010 9:37AM

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.

RyanSchneider · June 2, 2010 6:04PM

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.

Random Lengths · June 29, 2010 4:32PM

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.

David Atkinson · October 17, 2011 7:04PM

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

richard_collette@hsb.com · June 16, 2014 11:53AM

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.

daniel.rothig · June 18, 2014 8:04AM

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

Setting a Default for a New Column

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk