SQL SERVER – Using CASE Expression in Default Value

It is not possible to use CASE Expression in Default Values when you add a new column. I hope this answers the question. However, there is a workaround. Let us learn that today.

After reading the previous blog post SQL SERVER – Altering Column – From NULL to NOT NULL, I received a few emails and comments about whether the new column is populated based on the condition. Actually, it is not possible.

The workaround contains two steps.

Step 1: Add Column

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(100) NOT NULL DEFAULT ''

Step 2: Apply Condition Update

UPDATE TableName
SET ColumnName = 'FirstVal'
WHERE Col1 = 1;
UPDATE TableName
SET ColumnName = 'SecondVal'
WHERE Col1 = 2;
--... and so on...

Well, that’s it. Sometimes the question is complex but the answer is simple. I hope you enjoyed this simple blog post about case expression.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Menu
Exit mobile version