Robert Robert - 2 months ago 7
SQL Question

INSERT ALL INTO and Sequence.nextval for a Surrogate Key

I'm trying to insert 40 rows using an INSERT ALL INTO and I'm not certain on how to insert the surrogate key. Here's what I have

BEGIN
INSERT ALL

INTO question(question_id)
VALUES (question_seq.nextval)
END


Now if I add another INTO VALUES then I get a unique constraint violation.

BEGIN
INSERT ALL

INTO question(question_id)
VALUES (question_seq.nextval)

INTO question(question_id)
VALUES (question_seq.nextval)
END


How can I update the sequences nextval value for each INTO VALUES so that I can avoid the unique constraint violation? I assumed that nextval would automatically update itself.

UPDATE: I don't know if this is the best way to handle this but here's the solution I came up with:

first I created a function that returns a value
then I called that function in the id field of the VALUES clause

create or replace
FUNCTION GET_QUESTION_ID RETURN NUMBER AS
num NUMBER;
BEGIN
SELECT UHCL_QUESTIONS_SEQ.nextval
INTO num
FROM dual;
return num;
END GET_QUESTION_ID;

INSERT ALL
INTO question(question_id)
VALUES (GET_QUESTION_ID())
INTO question(question_id)
VALUES (GET_QUESTION_ID())

Answer

You can use something like this:

insert into question(question_id)
select question_seq.nextval from
(
    select level from dual connect by level <= 40
);

Although it's not a very convenient format, especially if there are other columns you want to add. You'd probably need to create another UNION ALL query, and join it by the LEVEL or ROWNUM.

My first thought was to do something like this:

insert into question(question_id)
select question_seq.nextval value from dual
union all
select question_seq.nextval from dual;

But it generates ORA-02287: sequence number not allowed here, due to the restrictions on sequence values.


By the way, are you sure your INSERT ALL works without a subquery? I get the error ORA-00928: missing SELECT keyword, and the diagram from the 11.2 manual implies there must be a subquery:

enter image description here