Add values from two existing column into new columns for a total

Question

We have two existing columns in our database named 'ClientIncome' and 'PartnerIncome'. We would like to add a new column named 'HouseholdIncome'

We would like to add the values from 'ClientIncome' and 'PartnerIncome' into the new 'HouseholdIncome' column?

Here is a small example of our database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] [float] NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Incomes] ON 

GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (1, 130.77, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (2, 294.82, 339.28, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (3, 0, NULL, NULL)
GO
INSERT [dbo].[Incomes] ([Id], [ClientIncome], [PartnerIncome], [HouseholdIncome]) VALUES (4, 0, 396.82, NULL)
GO
SET IDENTITY_INSERT [dbo].[Incomes] OFF
GO

The table we would like to update has around 5 million rows, so any help with adding this new column would be appreciated.

score 6 · Accepted Answer · answered Jul 31 '18 at 16:00

Just change your table create statement to use a computed column.

CREATE TABLE [dbo].[Incomes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientIncome] [float] NOT NULL,
    [PartnerIncome] [float] NULL,
    [HouseholdIncome] AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.)
)

If your table is already created, you can add a computed column:

ALTER TABLE dbo.Incomes ADD HouseholdIncome 
    AS COALESCE(ClientIncome, 0.) + COALESCE(PartnerIncome, 0.);

Add values from two existing column into new columns for a total

1 Answers1