How do I add a non-zero column, copy the data from another column

advertisements

Suppose I have a table A as follows:

dbo.A
____________
ID (PK, int, not null)
RequestID (FK, int, not null)
AssignedUserID (FK, nvarchar(128), not null)

Due to changes in process, we are adding a column 'IssuedUserID' and removing the 'not null' constraint on AssignedUserID. This table tracks something which is Issued by a user and then Assigned by a user, and due to changes in process, these are no longer required to be the same user.

Thus, for existing rows, IssuedUserID will match AssignedUserID. Moving forward, AssignedUserId will be null until the row is assigned.

I know I could write a script that will add IssuedUserID as a nullable field, update it to match AssignedUserId, and then alter it to be non-null, but I'd like to do it as part of the column creation if practical.

Is there a syntax to populate a row's new column's data based on another column's data?

We are using MS SQL Server 2012, if that matters.


You want to make the existing column nullable, and add a new non-nullable column. It is easier (and faster) to work the other way around; add a nullable column, then rename the columns.

ALTER TABLE dbo.A ADD NewUserID nvarchar(128) NULL
UPDATE dbo.A SET NewUserID = AssignedUserID
EXEC sp_rename 'dbo.A.AssignedUserID', 'IssuedUserID', 'COLUMN'
EXEC sp_rename 'dbo.A.NewUserID', 'AssignedUserID', 'COLUMN'

If necessary, add an index on AssignedUserID; any existing index on AssignedUserID has automatically gone to IssuedUserID.

As far as I know, the UPDATE statement is inevitable.