title: Getting new IDs after insert
body: <p>I'm inserting a bunch of new rows into a table which is defined as follows:</p>
  
  <pre><code>CREATE TABLE [sometable](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [someval] sometype NOT NULL
  )
  </code></pre>
  
  <p>using the following insert:</p>
  
  <pre><code>insert into sometable select somefield as someval from othertable
  </code></pre>
  
  <p>when I've finished, I'd like to know the IDs of all the newly inserted rows. <code>SCOPE_IDENTITY()</code> only returns the ID last row inserted.</p>
  
  <p>How can I get all the new IDs?</p>
  
  <p>One method that springs to mind would be to grab the current largest identity from sometable and the scope_identity() post-insert, and use these two values to select from sometable. For example:</p>
  
  <pre><code>declare @currentMaxId int;
  select @currentMaxId=MAX(id) from sometable
  insert into sometable select somefield as someval from othertable
  select * from sometable where id&gt;@currentMaxId and id&lt;=SCOPE_IDENTITY()
  </code></pre>
  
  <p>Is there a better pattern?</p>