title: SQL Set IDENTITY Field Using Variable
body: <p>All, I want to start the numbering of an <code>IDENTITY</code> field based on the current maximum obtained from another table. So I have tried something like the following</p>
  
  <pre><code>DECLARE @CurrentES INT;
  SET @CurrentES = (SELECT MaxES 
                    FROM [NDB]..[TmpMaxES]) + 1;
  ALTER TABLE BA 
  ADD ES INT IDENTITY(@CurrentES, 1);
  </code></pre>
  
  <p>But this will not accept a variable as the seed value in IDENTITY. How can what I require be achieved?</p>
  
  <p>Thanks for your time.</p>