SQL Server 2017 add new column with default value from another column in the same table

Question

I have a simple with column Weight1 which is of type float and not null. I want to add a new column Weight2 with default value from Weight1, so both Weight1 and Weight2 will be same.

Can anyone help how to do this?

I tried the following:

IF EXISTS
(
    SELECT 1
    FROM sys.tables
    WHERE Object_ID = OBJECT_ID(N'[dbo].[TableA]')
) AND NOT EXISTS
(
    SELECT 1
    FROM sys.columns
    WHERE Name = N'Weight2' AND
          Object_ID = OBJECT_ID(N'[dbo].[TableA]')
)
BEGIN
    PRINT 'Adding Weight2 to [dbo].[TableA]';   

    ALTER TABLE [dbo].[TableA] 
        ADD [Weight2] FLOAT NOT NULL 
END

The error I get is:

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column.

Column 'Weight2' cannot be added to non-empty table 'TableA' because it does not satisfy these conditions.

Thanks

Answer 1

The error clearly tells you: if you add a column with NOT NULL to a table that already has data in it, you MUST include a DEFAULT clause to define the default values for the newly added column - you are not doing that....

So try this:

ALTER TABLE [dbo].[TableA] 
    ADD [Weight2] FLOAT NOT NULL
    CONSTRAINT DF_TableA_Weight2 DEFAULT(0);

and then you can update Weight2 to get the same values as in Weight1:

UPDATE dbo.TableA
SET Weight2 = Weight1