2

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.

xyzwwq
  • 35
  • 1
  • 5

2 Answers2

4

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
Community
  • 1
  • 1
richaux
  • 2,622
  • 2
  • 35
  • 40
  • 1
    The correct (SQL Server) term for these is Computed Columns. "Derived column" is usually a phrase associated with select statements, to refer to columns which appear in the result set which don't appear in the base tables - a similar but disjoint concept – Damien_The_Unbeliever Apr 05 '11 at 07:44
  • oops, answer edited (ref [msdn](http://msdn.microsoft.com/en-us/library/ms191250.aspx)) – richaux Apr 05 '11 at 07:53
3

Are you using PHP or ASP.NET to enter the values? If the user is directly keying into the table, you can try using a trigger to set the last value.

However, Comp_Cont_Id is what is called a derived field. Its value depends on other fields. Examples of derived fields are the total number of records, average of prices and etc. It is better to come up with the field on the fly, dynamically, rather than to store it. Of course, there are exception to this, such as when performance is important.

Extrakun
  • 19,057
  • 21
  • 82
  • 129