Search code examples
sqlsql-serverdatabaset-sqldatabase-administration

Autofill a column according to another T-SQL statement

Sorry to disturb you again. lol :)

I am looking at the answers given on similar questions but they are still not very helpful.

Here's my case:

enter image description here

I want to change the schema on my table to auto fill one column.

What I understand is the update function is only for values, and would not apply when new values will be entered in the future.

I want to be able to set the Gname according to the Grade so that I can have. so that I can have 1 for novice or new, 2 for standard, 3- intermediate and 4- Expert.

Can someone help please? or direct me towards the right documentation.

Thanks to you all

Solution

  • typically this would be done through the use of a lookup table.

    So you would have your table you have then add another table for Gname that would carry the Grade Id 1,2,3,4 and the Name. Then when you want to add that as a column to your query you would do that through joining your gname table with your table on the ID.

    however since sql-server 2008 there is also a Computed Column https://msdn.microsoft.com/en-us/library/ms188300(v=sql.105).aspx

    If you want to enact the computed column you could alter your table and add it.

    ALTER TABLE TableName
ADD ComputedColumnName AS (CASE WHEN Grade = 1 THEN 'novice' WHEN Grade = 2 THEN 'standard'.....END)

    Yet another method some programmers use is to not put the lookup anywhere in the database but rather use an enumeration in their application layer to handle the translation.

    The first method is less demand of storage space in your database and doesn't require schema/table definition change to rename or add additional gnames. Plus it leads to more data integrity, because I have seen enumerations mess up a pretty significant eCommerce site and cause programmers a lot of headaches trying to debug.