title: SQL Server - how to manually increment a PK in multiple-row INSERT transaction
body: <p>I am working in SQL Server.  I have a table that has a PK int column.  This column does not have auto-increment enabled, and I am not allowed to change the schema.  I need to insert lots of rows (perhaps thousands) into this table manually.  None of the data inserted will come from any existing table.  However, I need to ensure that the PK column gets incremented by +1 for each new row.  My current script is like the following:</p>
  
  <pre><code>BEGIN TRAN
      INSERT INTO DB1.dbo.table1
      (PK_col, col1)
      VALUES
      (10, 'a')
      ,(11, 'something')
      ,(12, 'more text')
      ;
  </code></pre>
  
  <p>where I already know via a pre-query (<code>SELECT MAX(PK_col) + 1</code>) that PK_col is currently at 9.</p>
  
  <p>My problem is ensuring that the PK column gets incremented by +1 for each new row.  Because there could be thousands of rows to insert, I want to reduce the possibility of skipping values or a PK constraint violation being thrown.  I know that I can achieve this outside of the DB (via Excel), as long as I validate the PK values prior to running the SQL script.  However, I would like to create a solution that handles the auto-increment within the TRAN statement itself.  Is this possible (without running into a race condition)?  If so, how?</p>