Red Gate forums :: View topic - Setting a Default for a New Column
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

Setting a Default for a New Column

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
juliajuliabell



Joined: 02 Jan 2010
Posts: 7

PostPosted: Sat Jan 02, 2010 1:45 pm    Post subject: Setting a Default for a New Column Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Sun Jan 03, 2010 7:57 pm    Post subject: Re: Setting a Default for a New Column Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
juliajuliabell



Joined: 02 Jan 2010
Posts: 7

PostPosted: Mon Jan 04, 2010 10:36 am    Post subject: Re: Setting a Default for a New Column Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Mon Jan 04, 2010 10:56 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
jheidt



Joined: 08 Apr 2008
Posts: 8
Location: Somerset, NJ

PostPosted: Thu Jan 14, 2010 3:31 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
juliajuliabell



Joined: 02 Jan 2010
Posts: 7

PostPosted: Thu Jan 14, 2010 3:37 pm    Post subject: Re: Setting a Default for a New Column Reply with quote

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.
Back to top
View user's profile Send private message
RyanSchneider



Joined: 02 Jun 2010
Posts: 2

PostPosted: Wed Jun 02, 2010 11:04 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Random Lengths



Joined: 29 Sep 2008
Posts: 21
Location: Eugene, OR

PostPosted: Tue Jun 29, 2010 9:32 pm    Post subject: I agree Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Oct 18, 2011 12:04 am    Post subject: Reply with quote

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/viewtopic.php?p=51312#51312

Let us know if this satisfies your needs.

David Atkinson
Red Gate Software
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group