CODEWITHSUNDEEP
sql-server-2017
Mukil Deepthi
Mukil Deepthi

Reputation: 6452

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

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

Upvotes: 0

Views: 1666

Answers (2)

marc_s
marc_s

Reputation: 754408

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

Upvotes: 1

mrhloch
mrhloch

Reputation: 43

You can add the column and make it a computed column that gets the value from Weight1:

ALTER TABLE [TableA] ADD [Weight2] AS ([Weight1])  PERSISTED

Hope it helps.

Upvotes: 0

Related Questions