CODEWITHSUNDEEP
sqlsql-server
user1002346
user1002346

Reputation: 67

Bulk increment sequence within MS SQL Server

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?

Upvotes: 3

Views: 2667

Answers (2)

Clay
Clay

Reputation: 5084

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.

Upvotes: 0

Kittoes0124
Kittoes0124

Reputation: 5080

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.

Upvotes: 3

Related Questions