[SOLVED] Creating a column which should be auto generated by concatenating values in other columns in a table

Creating a column which should be auto generated by concatenating values in other columns in a table

I got a table in a SQL database with four columns: ID, Comp_Id, Cont_Id and Comp_Cont_Id. The values into ID, Comp_Id, Cont_Id columns are entered manually and the value in the column Comp_Cont_Id is to be generated automatically by concatenating the values in ID, Comp_Id, Cont_Id columns.

For example if we enter (201, 301, 401) into (ID, Comp_Id, Cont_Id) respectively, the value 201_301_401 should be auto-generated in the Comp_Cont_Id column of the table.

Is there a way to achieve it? If so please tell me.

Solution

MS SQL supports computed columns, the syntax is given in this similar question.

DROP TABLE test99
CREATE TABLE test99 (
    id int,  
    comp_id int,  
    cont_id int,
    comp_cont_id AS cast(id AS varchar) + '_' + cast(comp_id AS varchar) + '_' + cast(cont_id AS varchar)
) 

INSERT INTO test99 (id, comp_id, cont_id)
VALUES (201, 301, 401)

SELECT * FROM test99