Add new NOT NULL column with custom data.sql

/*
Run this script on the standard SimpleTalk demo database to create a new NOT NULL column but grab the data from a different column

(SSDT can't do this in an automated way)
*/
PRINT N'Adding [Person].[Address].[CreatedDate] column (empty)'
GO
ALTER TABLE [dbo].[Contacts]
    ADD [CreatedDate] [datetime] NULL
GO

PRINT N'Populating existing rows with data from the [ModifiedDate] column'
GO
UPDATE [dbo].[Contacts] -- <== State-based approach can't handle this
    SET [CreatedDate] = [ModifiedDate]
    WHERE [CreatedDate] IS NULL
GO

PRINT N'Adding default constraint...'
GO
ALTER TABLE [dbo].[Contacts]
    ADD CONSTRAINT [DF_Address_CreatedDate]
    DEFAULT (getdate())
    FOR [CreatedDate]
GO

PRINT N'Setting column to NOT NULL'
GO
ALTER TABLE [dbo].[Contacts]
    ALTER COLUMN [CreatedDate] [datetime] NOT NULL
GO