Query Several Nextval from Sequence in One Statement

Query several NEXTVAL from sequence in one statement

You can use this:

select your_sequence.nextval
from (
   select level 
   from dual 
   connect by level < 1000
);

How to select a list of 100 next sequence values from a database

Here is a link to an answer provided on a related question.

How to select multiple rows filled with constants?

The solution for Postgres, Oracle, and SQL Server are all different.

The Postgres solution is:

SELECT  l
FROM    generate_series(1, $n) l

In your case instead of select l you would select nextval('mySchema.mySequence')

Sequence next value in SELECT statement

Don't use a sub-select:

SELECT col1, co2, col3, TEST_SEQ.NEXTVAL as SEQ
FROM table; 

SQL Server get multiple next values for sequence

You can extract the right number of rows before to get the values for the sequence

DECLARE @N INT = 100;

SELECT next value FOR MY_SEQ 
FROM (
    SELECT 1 X
    FROM FN_NUMBERS(@N)
) X

CREATE FUNCTION [dbo].[FN_NUMBERS](
     @MAX INT
)
RETURNS @N TABLE (N INT NOT NULL PRIMARY KEY)  
BEGIN
     WITH
       Pass0 as (select '1' as C union all select '1'),       --2 rows
       Pass1 as (select '1' as C from Pass0 as A, Pass0 as B),--4 rows
       Pass2 as (select '1' as C from Pass1 as A, Pass1 as B),--16 rows
       Pass3 as (select '1' as C from Pass2 as A, Pass2 as B),--256 rows
       Pass4 as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B)    --65536 rows
       ,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C)  --4194304 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B)               --16777216 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B)               --4294836225 rows
     INSERT INTO @N
     SELECT TOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) AS N
     FROM Tally
     RETURN
END

Instead of tally table you can simply use sys.objects

SELECT next value FOR MY_SEQ 
FROM (
    SELECT TOP (@N) 1 X
    FROM sys.objects o1, sys.objects o2, sys.objects o3
) X

Selecting a sequence NEXTVAL for multiple rows

I ended up having to iterate through all the records and set the ID value individually. Messy and slow, but it seems to be the only option in this scenario.

Next N numbers from an SQL sequence

You could use recursive CTE:

WITH RECURSIVE cte AS (
  SELECT 1 AS v
  UNION ALL
  SELECT v+1
  FROM cte
  WHERE v < 3   -- here goes limit
)
SELECT seq.nextval
FROM cte;

DBFiddle Demo

---

Another approach is to set INCREMENT BY 3. Then you will get first number and could deduce next two values on application level.

EDIT:

Question is tagged to Oracle and above solution will not work in oracle. We can achieve recursion in Oracle not by this way rather making multiple CTE.

Oracle supports recursive CTE as well.

WITH cte(v) AS (
  SELECT 1 FROM dual
  UNION ALL
  SELECT v+1
  FROM cte
  WHERE v < 3
)
SELECT seq.NEXTVAL
FROM cte;

DBFiddle Demo

Same seq_name.nextval in one query. ORACLE

These are all great answers, unfortunately it was just not enough. Will definitely be able to return to this page when struggling in the future.

I have gone with a different method. Asked a new question and got my answer.

You can go check it out here.

Select multiple ids from a PostgreSQL sequence

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

generate_series is a function which returns many rows with sequential numbers, configured by it's arguments.

In above example, we don't care about the value in each row, we just use generate_series as row generator. And for each row we can call nextval. In this case it returns 3 numbers (nextvals).

You can wrap this into function, but I'm not sure if it's really sensible given how short the query is.

---

Related Topics