Bulk increment sequence within MS SQL Server

Question

Postgres and Oracle include generate_series/connect by command to allow incrementing a sequence by more than 1. I have a need to increment a sequence by a variable amount before row insertion. For example in Postgres this would look like the query below:

select nextval('mytable_seq') from generate_series(1,3);

What would be the recommended way to accomplish this in Microsoft SQL Server?

Why do you want to do this? Just using `identity` and give an argument for the increment amount. — Gordon Linoff, Apr 07 '17 at 20:40
@GordonLinoff see http://stackoverflow.com/a/10062436/1186165. The fact that an identity is "tied" to a table is a major limitation in some settings. — Kittoes0124, Apr 07 '17 at 20:45

Kittoes0124 · Accepted Answer · 2017-04-07T20:46:08.980

3

There is a stored procedure call you can use. Alternatively, you could set up some sort of while loop that calls next value for multiple times and caches them for use later.

edited Apr 07 '17 at 20:46

answered Apr 07 '17 at 20:40

Kittoes0124

4,930
3
26
47

score 0 · Answer 2 · answered Jun 08 '20 at 15:47

Old question - new answer:

Assuming you've defined your sequence as:

create sequence dbo.IdSequence 
as bigint 
start with 1

...you can just include the phrase next value for dbo.IdSequence as a column in a select statement. When I have a sequence values I want to be paired to a result set, I'll do something like:

select
   next value for dbo.IdSequence as Seq,
   someSource.Col1,
   someSource.Col2  --> ... and so on
from
   dbo.someSource

If I have a need for a specific number of sequence values, I'll use some kind of sql table-valued function that generates dummy values:

select 
    next value for dbo.IdSequence Seq
from
    dbo.FromTo( 1, 5 )

Note that if you make two columns requesting values from the same sequence, they'll return the same value for each column. It's probably not what you want:

select 
    next value for dbo.IdSequence Seq1,
    next value for dbo.IdSequence Seq2
from
    dbo.FromTo( 1, 5 )

...returns something like:

Seq1    Seq2
--------------------------
549     549
550     550
551     551
552     552
553     553

The FromTo is a simple function that generates numbers. There are lots of great examples of functions that do this in (lots of) answers to this question.

Bulk increment sequence within MS SQL Server

2 Answers2