Search code examples
sqlsql-updateauto-increment

SQL update records with incrementing value starting from 1 each time

I am adding batches of records to a table using a single insert statement. I want each new batch to be allocated incrementing numbers, but starting from 1 each time.

So, if I have

Batch    Name    IncementingValue
1        Joe     1
1        Pete    2
1        Andy    3
2        Sue     1
2        Mike    2
2        Steve   3

and I then add two records (using a single insert statement) :

3        Dave
3        Paul

How can I run an update statement against this table so that Dave will be set to 1 and Paul to 2. I don't want to use a cursor.

Solution

  • The ranking function ROW_NUMBER should do what you need. You didn't mention any specific rules about how the sequence number should be allocated, so I've done it here using the name:

    INSERT targetTable(Batch,Name,IncementingValue)
SELECT BatchId,
       Name,
       ROW_NUMBER() OVER (ORDER BY Name)
FROM sourceTable