title: Add a new column to an existing table, with a value equal to the ID
body: <p>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:</p>
  
  <pre><code>CREATE TABLE [dbo].[Status](
      [Status_ID] [int] IDENTITY(1,1) NOT NULL,
      [Description] [nvarchar](80) COLLATE Latin1_General_CI_AS NOT NULL
  )
  </code></pre>
  
  <p>The column I want to add is also of type INT and nulls are not allowed. Furthermore, I want the <strong>initial</strong> values for this column to be equal to the ID (it cannot be a computed column).</p>
  
  <p>This is the script I have written for this:</p>
  
  <pre><code>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 &lt;= @RowCount)
  BEGIN
          UPDATE Status
          SET Status_Code = @I
          WHERE Status_ID = @I
          //Increment the iterator
          SET @I = @I  + 1
  END
  </code></pre>
  
  <p>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?</p>