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

Question

In my database in a sql-server 2005 instance, I need to add a new column to a table with existing data. Currently, the table is as follows:

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 valid. In addition, I want the initial values for this column to be equal to the identifier (it cannot be a computed column).

This is the script I wrote 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 works fine. However, this seems like a lot of code for a rather small task. Does anyone know a more efficient way to code this?

Answer 1

? :

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

UPDATE Status 
SET Status_Code = Status_ID 

Answer 2

.

, .

.

Answer 3

I would go with Gerrie's answer as you want the column to be NOT NULL. If you specify a default value, SQL Server will still allow you to leave this column in any subsequent insert statement (the default value will be assigned to the column)