Insert and alter data based on condition SQL Server

Question

I currently have Table1 that includes

 ID Gender
 1  m
 2  f
 3  f
 4  m
 5  f
 6  f

Now i am looking to insert this data into a second table (Table2). But basically i am trying to insert it with the words 'Male' and 'Female'. So for example when Gender = 'm' in table 1 i want to insert the char 'Male' into the second table (table2) and the same for female.

I created some code such as

 UPDATE Table2
 SET 
 Gender =
 ( CASE WHEN Table1.Gender = 'm' THEN 'Male'
 WHEN Table1.Gender = 'f' THEN 'Female'
 ELSE 'N/a'
 END)

I have an SQL Fiddle schema if that is of any help -

http://sqlfiddle.com/#!9/8a3080

Thanks

Answer 1

You could use INSERT statement rather than UPDATE by Using Case expression :

INSERT INTO table2
       SELECT id,
              CASE(Gender)
                  WHEN 'm'
                  THEN 'Male'
                  WHEN 'f'
                  THEN 'Female'
              END
       FROM table1;

Result (table2):

ID  Gender
1   Male
2   Female
3   Female
4   Male
5   Female
6   Female

Answer 2

If you want to insert rows into the second table, use insert, not update:

insert into table2(id, gender)
    select t1.id,
           (case when t1.gender = 'f' then 'female'
                 when t1.gender = 'm' then 'male'
            end) as gender_expanded
    from table1 t1;