title: Create column and insert into it within the same transaction?
body: <p>Is it possible to create a column and insert values to it during the same transaction? This is part of an upgrade script. I found the following method <a href="http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/" rel="noreferrer">online</a>, but it does not work; I get an error: <code>Invalid column name 'IndexNumber'.</code>. I'm assuming this is because the transaction hasn't created the column yet so there is nothing to insert to.</p>
  
  <p>The relevant parts of my script:</p>
  
  <pre><code>Print 'Beginning Upgrade'
  Begin Transaction
      -- --------------------------------------------------------------------
      USE [MyDatabase];
  
      /* Widgets now can be ordered and the order can be modified */
      ALTER TABLE [dbo].[Widgets] ADD [IndexNumber] [int] NULL;
  
      DECLARE @ind INT 
      SET @ind = 0 
      UPDATE [dbo].[Widgets]
      SET @ind = [IndexNumber] = @ind + 1;
  
      ALTER TABLE [dbo].[Widgets] ALTER COLUMN [IndexNumber] [int] NOT NULL;
      -- --------------------------------------------------------------------
  Commit tran
  Print 'Upgrade completed'
  </code></pre>
  
  <p>The reason why <code>[IndexNumber]</code> is not an identity column is that it must be editable.</p>