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?
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.
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.
identityand give an argument for the increment amount.