When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.

The naive approach would be to query the data, increment it, and then use that for an insert.

For example:

  (SELECT MAX(ID) FROM myTable) + 1,
  "My actual data."

But I'm unsure if this is actually the best approach. Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.

Edit: Sql Server version 2012.

Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.

  • What version are you using? Jan 21, 2015 at 19:55
  • 2012, I'll add it to the question. Jan 21, 2015 at 20:02
  • Of course there is. Just a wild idea: put the id-generator in a trigger and lock the whole table when generating the ID (check the docs for the most appropriate lock type). Of course if you have many concurrent inserts that will slow everything down a bit, but locking is the only way to ensure that no concurrent INSERTs can happen.
    – watery
    Jan 21, 2015 at 20:20
  • Is putting an identity field on the table out of the question? Jan 23, 2015 at 14:09
  • @SteveMangiameli Near enough to out of the question. Management will be more likely to shelve the project than change the production database in this way (because of rolling changes to other systems using the same database). Jan 23, 2015 at 14:43

4 Answers 4


The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain.

Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest benefits here is that you can also specify a number of values to be cached for faster retrieval if you're working with rapid inserts.

    START WITH 1050
    CACHE 1000

Then to get the next ID value, use:


Which will provide 1050, then 1051, and so on and so forth.

MS Docs for full information on other properties.

  • I'm familiar with sequences from using them for identities in Oracle. The problem is I'm trying to do this as a db_datawriter but not a db_ddladmin. Jan 21, 2015 at 20:35
  • If using IDENTITY and SEQUENCE are out as options, I think you're more in the area of a hokey solution than best practices. Jan 21, 2015 at 20:38
  • 1
    I guess this is more of a 'best practice given a bad situation' deal. I'll still prefer the best hokey solution over any old hokey solution. Jan 21, 2015 at 20:41
  • This method can have concurrency issues? Thanks in advance for answer Mar 31, 2016 at 16:02

Given the answers above. If this is a one-off bulk insert, I'd create a temporary table with an identity column. Insert the data to it. And then select the data and insert to your target table.

You could set the identity start value, or start at 1 and add a fixed value when you do the final insert.

I can't check atmo but a believe data writer privileges should allow you to create your own #tables


I'm putting this as an answer because it's too long as a comment. It makes a lot of assumptions, but this could be one method. It's definitely a bit hokey but should work fairly reliably without creating objects in your target database. I think a temp table or other staging area may be required depending on what's doing the bulk import for you, but here is my thoughts.

1) Figure out how many records you are going to insert (bulk insert into a temp table?).

2) Get current MAX(ID) from Target Table

3) Add a dummy record to target table with MAX(ID) + 1 + RecordCount. NOTE: this assumes that the other inserts going on in this table are using MAX(ID) + 1

4) Make sure that nothing inserted between your now reserved range. This should avoid concurrency issues.

5) Assign values using MAX(ID) + Row_Number to your temp table or during bulk or whatever.

6) Insert into target table specifying the ID values. Making sure to overwrite your dummy record


Certainly, it will work:

SELECT MAX(ID)+1, "My actual data" FROM myTable;
  • 1
    Welcome to DBA.SE. Please take the time to read the question. The original poster (OP) knows that the statmeent will work. That is not the question and hence this is not an answer to the (hidden) question: "But I'm unsure if this is actually the best approach."
    – John K. N.
    Jan 15, 2020 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.