Add a new column to an existing table, with a value equal to the ID

Question

In my database in a sql-server 2005 instance, I need to add a new column to a table with existing data. The table currently looks like this:

CREATE TABLE [dbo].[Status](
    [Status_ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](80) COLLATE Latin1_General_CI_AS NOT NULL
)

The column I want to add is also of type INT and nulls are not allowed. Furthermore, I want the initial values for this column to be equal to the ID (it cannot be a computed column).

This is the script I have written for this:

ALTER TABLE [dbo].[Status]
add Status_Code int NOT NULL DEFAULT 1

GO
//Get the number of rows
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(Status_ID) FROM Status) 

//Create an iterator
DECLARE @I INT
SET @I = 1

//Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
        UPDATE Status
        SET Status_Code = @I
        WHERE Status_ID = @I
        //Increment the iterator
        SET @I = @I  + 1
END

This script seems to work perfectly. However, it seems like a lot of code for a rather small task. Does anyone know of a more efficient way to code this?

Answer 1

Why loop through the table to do the update? Just do this:

ALTER TABLE [dbo].[Status] 
add Status_Code int NOT NULL DEFAULT 1 

UPDATE Status 
SET Status_Code = Status_ID 

Answer 2

Create the new column, and make it nullable.

Then write a simple update statement to insert the existing id's into the new columns.

Then make your column not-nullable.