inserting into table and incrementing a column based on current highest number

Question

I have a table that has a bunch of columns, one of which is named [pos]. That column contains a number. When inserting a new record, i want to be able to insert my new record but with the [pos] being 1 higher than the highest [pos] already in the db.

Insert into table(c1,c2,pos) VALUES('c1','c2',?)

I'm using MSSQL

This is **very** problematic. You run the risk of two processes attempting to add a row at the "same time", getting the same value for max [pos], and attempting to add two different rows with the same pos value. Probably your best bet would be something like my answer below, but I still don't like the solution. — Laughing Vergil, Apr 07 '17 at 16:43

score 1 · Accepted Answer · answered Apr 07 '17 at 15:01

1

You can just pull the Max(Pos) and add 1 to it:

Insert  Table
        (C1, C2, Pos)
Select  'C1', 'C2', Max(Pos) + 1
From    Table

answered Apr 07 '17 at 15:01

Siyual

16,415
8
44
58

I'll give that a shot! – Damien Apr 07 '17 at 15:04
1

Could this technique run into [concurrency issues](http://stackoverflow.com/questions/8956044/avoiding-concurrency-problems-with-max1-integer-in-sql-server-2008-making-ow)? – David Rushton Apr 07 '17 at 15:11

score 1 · Answer 2 · answered Apr 07 '17 at 15:02

1

SQL Server can manage this for you. Make Pos an identity column.

Example

-- Creating a identity column.
DECLARE @Table TABLE
(
    C1  VARCHAR(2),
    C2  VARCHAR(2),
    Pos INT IDENTITY (1, 1)
);

-- Populating the table.
-- NOTE: SQL Server takes care of Pos for you.
INSERT INTO @Table 
(
    C1, 
    C2
)
VALUES
    ('C1', 'C2'),
    ('C3', 'C4')
;

Returns

C1  C2  Pos
C1  C2  1
C3  C4  2

answered Apr 07 '17 at 15:02

David Rushton

4,915
1
17
31

I'm not sure why I've been downvoted. If you don't add a comment, I can't fix whatever it is. – David Rushton Apr 07 '17 at 15:06
1

This is actually the best approach to solving this problem. The downvote is misguided. – Gordon Linoff Apr 07 '17 at 15:07
I agree with @GordonLinoff. The only other option that doesn't pose potential race conditions would be to use a sequence. – Sean Lange Apr 07 '17 at 15:08
1

It's not necessarily sequential. It's a column that's controlled by a user so pos=1 today might be pos=7 tomorrow – Damien Apr 07 '17 at 15:09
This is sounding more like you want to use ROW_NUMBER when you retrieve data instead of storing it. – Sean Lange Apr 07 '17 at 15:25
If this is a user-defined position, then why would it be based on the previously added row? – Laughing Vergil Apr 07 '17 at 16:40
Could you provide an example where pos=1, and then later 7. Can you detail the steps that lead to this change? As things stand, I think I've misunderstood your question. – David Rushton Apr 07 '17 at 18:46

SqlZim · Answer 3 · 2017-04-07T15:10:37.887

Well, the direct way for a single row:

insert into table(c1,c2,pos) 
select 'c1','c2',1+isnull((select max(pos) from table),0)

For more than one set of values you could do something like this:

create table t (c1 char(2), c2 char(2), pos int);

insert into t (c1,c2,pos) 
select v.c1,v.c2,row_number() over (order by (select 1))+x.MaxPos
from (values ('c1','c2'),('d1','d2'),('e1','e2')) v (c1,c2)
cross join (
  select MaxPos = isnull(max(pos),0) 
  from t
  ) x;

select * from t;

rextester demo: http://rextester.com/DVJB25196

returns:

+----+----+-----+
| c1 | c2 | pos |
+----+----+-----+
| c1 | c2 |   1 |
| d1 | d2 |   2 |
| e1 | e2 |   3 |
+----+----+-----+

score 0 · Answer 4 · answered Apr 07 '17 at 16:53

There are several ways to do this. However, we really need more information than you are providing. Specifically, (1) can you have two entries in the table with the same value for [pos], and (2) Can the user ever specify that the new [pos] value is lower than an existing value in the table? For the purposes of this solution, I will assume that the answer to both of these questions is "NO".

Using an IDENTITY field is the most common, although based on your specification you might have to reset the base of the IDENTITY multiple times, which could be confusing and problematic.

A SEQUENCE could be used in a similar way, with you having to reset the base of the sequence as needed.

If you really must look at the largest value in the table (especially significant if you can delete rows that might have the highest [pos] value), then you probably need to put a UNIQUE constraint on the [pos] column, use transactions for every insert or delete, and use TABLOCK and ROWLOCK hints when getting the maximum value or deleting rows. Making sure that you begin a transaction and read the highest [pos] value only in SQL with a ROWLOCK or TABLOCK hint, only to immediately add the new row, commit the transaction, and ensure that the locks are released is the only way to do what you seem to be asking for.

inserting into table and incrementing a column based on current highest number

4 Answers4