July 19, 2005 at 10:07 am
Thanks for your time to stop by this query.
I have a table that has a huge set of records already and I need to add a new column to this table. This new column says 'NewCol' would have a value same as a existing column 'Old Col' in 75% of the cases and will have a new value in the 25% of the cases. The sources for inserting into this table are several. Rather than modifying all the sources to iinsert this value, is there any other preferred way to go? I tried using a computed column for this new one indicating 'New Col' = 'Old Col', but it doesn't allow me to force a value for the 25% of the cases where the formula doesn't hold good. Please suggest on any other better alternative for this rather than modifying all the source processes.
Thank you.
July 19, 2005 at 11:21 am
Would your logic work with a "case" statement?
Update table
set "New Col'"=case when "logic" then 'value' else "Old Col" end
from "source tables" where ....
I would look to update the table for the past records to catch up your history and then look into putting the case statement in the select during your ETL or use a trigger for future updates.
July 19, 2005 at 12:01 pm
I am not sure if my question has been understood clearly.
I believe you are suggesting to use a udf or a trigger to update this column. I do not want to use a trigger as this table is critical. The value for this column is known only based on the source which performs the insert. Would have to include the logic in several processes which inserts the value to achieve this. Since the value is going to differ from the 'OldCol' only in 25% of the cases, I was wondering if a computed column for the 75% of the cases would make sense. Please reply on if it is possible to override the computed column value by any option during the 25% of the cases. If this works I could change only the source which is responsible for the 25% to do a forced insert and keep the rest to use the formula. Hope its clear, please post any question. Thanks.
July 21, 2005 at 8:33 am
No, it is not possible to directly change the value of a computed column, as the data is not stored separately. Sounds like what you need is a default instead of a computed column, though I'm not sure if you can default a column to a value in another column. Your application logic will need to change.
Dylan Peters
SQL Server DBA
July 21, 2005 at 3:29 pm
that probably sounds like my alternative. To define a default and then force the value only for the 25% of my application.
But can any of you tell me if it is possible to define a default for a column to use the value from another column on the same row? I tried and looks like whatever is supplied for a default seems to be taken as a constant. Tried with a User defined default also. Please help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply