www.webdeveloper.com
Results 1 to 3 of 3

Thread: MS SQL 2005 - set sum of three columns as default value

  1. #1
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582

    MS SQL 2005 - set sum of three columns as default value

    Hello, everyone.

    In MS SQL Server 2005, using SQL Server Management Studio, when creating/editing/designing a new table, is it possible to set a column default value to be the sum of 2 or 3 other columns in the same table?

    Let's say I have a table where most of the columns are smallint datatypes.
    Code:
    ID   Month    Year    ProdA    ProdB    ProdC    Total
    1    Oct     2010       44        22      11       77
    2    Nov     2010       22        11       1       34
    How can I set the default value of "Total" to be the sum of ProdA, ProdB, and ProdC?

    Thanks,

    ^_^

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    Have a look at Computed columns.

    Code:
    Create Table t1 (
    	[ID] int identity (1, 1) NOT NULL,
    	[Month] char(3) NOT NULL,
    	[YEAR] smallint NOT NULL,
    	[ProdA] int NOT NULL,
    	[ProdB] int NOT NULL,
    	[ProdC] int NOT NULL,
    	[Total] as (ProdA + ProdB + ProdC)
    );

  3. #3
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Awesome! Thanks, russell.

    Will the same format work if I'm creating the table directly in Server Management Studio? I'll give that a shot.

    Aw'ra best,

    ^_^

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles