Concatenate multiple columns into new column

Question

Assume the folllowing table users

first_name   last_name   gender
-------------------------------
Britney      Spears      F
Mick         Jagger      M
Beyonce      Knowles     F
------------------------------- 

I want to concatenate the values of this table and add them to a new colum called total, as follows:

first_name   last_name   gender   total
---------------------------------------------------
Britney      Spears      F        Britney Spears F
Mick         Jagger      M        Mick Jagger M
Beyonce      Knowles     F        Beyonce Knowles F
---------------------------------------------------

I have been trying multiple variations on:

select into [users] concat([first_name], [last_name], [gender]) as [total]
from   [users]

But this does not seem to work. What am I doing wrong?

Answer 1

I think you want a new column:

alter table add total varchar(max);  -- or whatever length

update users
    set total = concat([first_name], [last_name], [gender]);

Or better yet, add a computed column:

alter table add total as concat([first_name], [last_name], [gender]);