SQL COMMANDS to add a new column

Question

I have customer table with column First_Name and Last_Name of Customer, I want to combine these two column with one Customername and add this Customer_Name column in the table also .and want to remove first_name and Last_name column from the table .

select First_name, Last_name, concat(First_name,' ',Last_name) as Customer_Name from Customer;

alter table Customer add CustomerName varchar(50);

insert into Customer (CustomerName) values (First_name + ' ' + Last_name);

select * from Customer;

alter table Customer add CustomerName1 varchar(50);

insert into Customer (CustomerName1) values (convert(varchar(50),First_name + ' ' + Last_name);

these are the command which i already tried but error

Answer 1

Here how to do it :

Add CustomerName column :

ALTER TABLE Customer add CustomerName varchar(50);

Then, rather than inserting, you must update:

UPDATE Customer
SET CustomerName = First_name + ' ' + Last_name;

Then drop columns :

ALTER TABLE Customer DROP COLUMN First_name;

ALTER TABLE Customer DROP COLUMN Last_name;

Answer 2

I would recommend you not create a dedicated column that combines the two name fields, and I would certainly not advise you drop the original fields. You're always going to be better off storing data in atomic fields (i.e. first and last name) rather than combined fields. For instance, you can construct the value for CustomerName from the first_name and last_name fields, but if you then drop those fields, and decide you want to change the way you treat a combined name, you're out of luck. Or rather you have to rely on string jiu jitsu to hope you can extract the original fields from eachother. There's not really any need to remove the individual fields; if you don't want to use them for a given query, just don't use them.

I would recommend you create a computed_column like so:

alter table Customer
add CustomerName as first_name + ' ' + last_name

Here, the field is entirely constructed from the values of first_name and last_name, and you never had to throw away those nice, atomic columns. Plus, if in the future you decide you want to change how CustomerName gets built, you just re-create the computed column with a different expression. You can even use the persisted keyword on the computed column if, say, you want to index the newly created CustomerName field.