CODEWITHSUNDEEP
sqloracle-database
Vaibhavkrishna Bhosle
Vaibhavkrishna Bhosle

Reputation: 41

Using sequence next val in sql to enter all values from the sequence to row of a table

CREATE SEQUENCE pri_series_new
    START WITH 100000
    INCREMENT BY 1
    MINVALUE 000000
    MAXVALUE 99999999
    cycle

i need all the values this sequence generates into a table's row.

INSERT into pri_temp VALUES(pri_series_new.nextval);

this inserts only one value at a time i need all the values to be inserted.

the row in the table should contain all the numbers that a sequence object is generating.

Upvotes: 0

Views: 1175

Answers (4)

eifla001
eifla001

Reputation: 1157

You can also do it like this.

INSERT into pri_temp
select pri_series_new.nextval
  from dual
 connect by rownum <= (SELECT max_value FROM all_sequences WHERE lower(sequence_name) = 'pri_series_new')

Upvotes: 0

Popeye
Popeye

Reputation: 35900

I think you should use the following version of SQL.

Note: I have used INCREMENT_BY to handle the situation where INCREMENT_BY of the sequence is other than 1.

INSERT INTO PRI_TEMP
    SELECT
        PRI_SERIES_NEW.NEXTVAL
    FROM
        (
            SELECT
                ( MAX_VALUE - LAST_NUMBER + INCREMENT_BY ) / INCREMENT_BY AS LOOP_CNT
            FROM
                USER_SEQUENCES
            WHERE
                SEQUENCE_NAME = 'PRI_SERIES_NEW'
        )
    CONNECT BY
        LEVEL <= LOOP_CNT;

db<>fiddle demo

Cheers!!

Upvotes: 0

Levente Tak&#225;cs
Levente Tak&#225;cs

Reputation: 445

I think this should work for you:

DECLARE
    maxvalue number;
BEGIN
    select MAX_VALUE into maxvalue from all_sequences where sequence_name='PRI_SERIES_NEW';
    FOR i IN 1..maxvalue LOOP
        EXECUTE IMMEDIATE 'INSERT into pri_temp VALUES(PRI_SERIES_NEW.nextval)';
    END LOOP;

END;

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

You don't actually need a sequence, you just need a row-source...

insert 
  into pri_temp
select seq     
  from (select level as seq from dual connect by level <= 99999999)
 where seq >= 100000;

Upvotes: 2

Related Questions