0

I have a table with one String column. I want to insert another column null that the value of the lines already inserted on the DB will be filled with a "y" or "n" based if the String column is filled or not. Is it possible to do?

actually it won't have a default value. i just need to know if is possible to set these values based on a if command

Gondim
  • 3,038
  • 8
  • 44
  • 62
  • 1
    I _think_ I understand what you mean, but could you maybe edit your post with an example with two or three rows before and after this transformation? – Mat Apr 19 '11 at 14:49
  • Is the other column already populated? Do you want to insert values for new columns as they come in? Why do you need to add a column for a calculated value like this? – Oded Apr 19 '11 at 14:50
  • its running like this in production, I want to switch it, but don't want to have null pointers or anything like that, do you knopw what i mean? – Gondim Apr 19 '11 at 14:56
  • calculating this with a select query rather than adding a column would be preferable. You can make a view from the select query if you need tabular representation. – reggie Apr 19 '11 at 15:02

3 Answers3

3

You can add a computed column:

ALTER TABLE
        mytable
ADD     is_string_filled AS (CASE WHEN string_field IS NULL THEN 'N' ELSE 'Y' END)
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • how can I add a column after another column for example, i want to insert column 3 between columns 1 and 2 – Gondim Apr 19 '11 at 14:53
  • 1
    @Pringlesinn - you can't without remaking the table. It's not important since you can say `SELECT col2, col3, col1` to change the order. – JNK Apr 19 '11 at 14:58
  • @pringlesinn: you can't. But you can just change the column order in the query (and in fact just do this calculation in the query). – Quassnoi Apr 19 '11 at 14:59
  • The column order should not be important. @JNK +1 for that comment – reggie Apr 19 '11 at 14:59
3
//You can implement this in a select query as well instead of adding a column.
select strColumn, 
CASE 
    WHEN strColumn is null THEN 'N'
    ELSE 'Y'
END as colCheck

FROM TABLE1;

You can know more about using conditional clause in sql out here : How do I perform an IF...THEN in an SQL SELECT?

Calculating the above problem with a slect query rather than adding a column would be preferable. You can make a view from the select query if you need tabular representation.

Community
  • 1
  • 1
reggie
  • 13,313
  • 13
  • 41
  • 57
1

Use Computed Columns ...

http://msdn.microsoft.com/en-us/library/ms191250.aspx

Akram Shahda
  • 14,655
  • 4
  • 45
  • 65