Setting a Default for a New Column

Forum for users of SQL Compare schema synchronization utility

Moderators: JonathanWatts, Chris Auckland, David Atkinson

Setting a Default for a New Column

Postby juliajuliabell » Sat Jan 02, 2010 1:45 pm

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.
juliajuliabell
 
Posts: 7
Joined: Sat Jan 02, 2010 1:22 pm

Re: Setting a Default for a New Column

Postby David Atkinson » Sun Jan 03, 2010 7:57 pm

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re: Setting a Default for a New Column

Postby juliajuliabell » Mon Jan 04, 2010 10:36 am

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.
juliajuliabell
 
Posts: 7
Joined: Sat Jan 02, 2010 1:22 pm

Postby David Atkinson » Mon Jan 04, 2010 10:56 am

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby jheidt » Thu Jan 14, 2010 3:31 pm

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?
jheidt
 
Posts: 8
Joined: Tue Apr 08, 2008 1:08 pm
Location: Somerset, NJ

Re: Setting a Default for a New Column

Postby juliajuliabell » Thu Jan 14, 2010 3:37 pm

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.
juliajuliabell
 
Posts: 7
Joined: Sat Jan 02, 2010 1:22 pm

Postby RyanSchneider » Wed Jun 02, 2010 11:04 pm

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.
RyanSchneider
 
Posts: 2
Joined: Wed Jun 02, 2010 10:58 pm

I agree

Postby Random Lengths » Tue Jun 29, 2010 9:32 pm

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.
Random Lengths
 
Posts: 27
Joined: Mon Sep 29, 2008 11:02 pm
Location: Eugene, OR

Postby David Atkinson » Tue Oct 18, 2011 12:04 am

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
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Not sufficient

Postby richard_collette@hsb.com » Mon Jun 16, 2014 4:53 pm

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.
richard_collette@hsb.com
 
Posts: 5
Joined: Fri Jun 08, 2012 2:29 pm

Postby daniel.rothig » Wed Jun 18, 2014 1:04 pm

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
daniel.rothig
 
Posts: 1
Joined: Tue Dec 18, 2012 11:04 am


Return to SQL Compare Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests