title: INSERT ALL INTO and Sequence.nextval for a Surrogate Key
body: <p>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 </p>
  
  <pre><code>BEGIN
     INSERT ALL 
  
     INTO  question(question_id)
     VALUES (question_seq.nextval)
  END
  </code></pre>
  
  <p>Now if I add another INTO VALUES then I get a unique constraint violation.</p>
  
  <pre><code>BEGIN
     INSERT ALL 
  
     INTO  question(question_id)
     VALUES (question_seq.nextval)
  
     INTO  question(question_id)
     VALUES (question_seq.nextval)
  END
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>UPDATE</strong>: I don't know if this is the best way to handle this but here's the solution I came up with:</p>
  
  <p>first I created a function that returns a value
  then I called that function in the id field of the VALUES clause</p>
  
  <pre><code>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())
  </code></pre>