title: INSERT INTO statement that copies rows and auto-increments non-identity key ID column
body: <p>Given a table that has three columns </p>
  
  <ol>
  <li>ID (Primary Key, not-autoincrementing)</li>
  <li>GroupID</li>
  <li>SomeValue</li>
  </ol>
  
  <p>I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that has one GroupID into a new GroupID.</p>
  
  <p>Example beginning table:</p>
  
  <pre><code>ID | GroupID | SomeValue
  ------------------------
  1  |    1    |    a
  2  |    1    |    b
  </code></pre>
  
  <p>Goal after I run a simple INSERT INTO statement:</p>
  
  <pre><code>ID | GroupID | SomeValue
  ------------------------
  1  |    1    |    a
  2  |    1    |    b
  3  |    2    |    a
  4  |    2    |    b
  </code></pre>
  
  <p>I thought I could do something like:</p>
  
  <pre><code>INSERT INTO MyTable
  (       [ID]
         ,[GroupID]
         ,[SomeValue]
  )
  (
  SELECT (SELECT MAX(ID) + 1 FROM MyTable)
         ,@NewGroupID
         ,[SomeValue]
   FROM MyTable
   WHERE ID = @OriginalGroupID
  )
  </code></pre>
  
  <p>This causes a PrimaryKey violation since it will end up reusing the same Max(ID)+1 value multiple times as it seems.</p>
  
  <p>Is my only recourse to a bunch of INSERT statements in a T-SQL WHILE statement that has an incrementing Counter value?</p>
  
  <p>I also don't have the option of turning the ID into an auto-incrementing Identity column since that would breaking code I don't have source for.</p>