4

I was hoping to call NEXTVAL once and use the returned values multiple times. However it doesn't work.

Assume the sequence seq_name is at 10 now (queries are executed independently, not in listed order):

SELECT NEXTVAL('seq_name'), NEXTVAL('seq_name');

The above will give 11, 12 (I want 11 and 11 again).

SELECT NEXTVAL('seq_name'), CURRVAL('seq_name');
SELECT NEXTVAL('seq_name'), LASTVAL();

The above will give 11, 10 OR give an error that NEXTVAL was never called in the current session. [ I know why the error happens :) ]

Is there a way to use NEXTVAL and CURRVAL in the same SQL statement to get the same incremented value (11 in this case) for reuse? Or a simple solution to this problem?

ADTC
  • 8,999
  • 5
  • 68
  • 93

1 Answers1

6

A possible solution

SELECT nextval nextval1, nextval nextval2
  FROM
(
  SELECT NEXTVAL('seq_name') nextval
) q

Here is SQLFiddle demo.

UPDATE To insert instead of INSERT INTO ... VALUES use INSERT INTO ... SELECT

INSERT INTO Table1 (col1, col2, col3, col4, ...) 
SELECT nextval, nextval, 5, 'Text value', ...
  FROM
(
  SELECT NEXTVAL('seq_name') nextval
) q
peterm
  • 91,357
  • 15
  • 148
  • 157
  • 1
    How about an `INSERT () VALUES ()` construct? I need it for such an insert statement, therefore a more general solution would be apt. Your solution is too specific to SELECT statements. – ADTC Jun 21 '13 at 10:31
  • Thanks, nice answer, but what if I really _really_ ***really*** have to stick to the INSERT VALUES construct? :D – ADTC Jun 21 '13 at 10:36
  • 2
    There is no situation (at least I'm not aware of) when you can't replace `INSERT VALUES` with `INSERT SELECT` :) – peterm Jun 21 '13 at 10:37
  • Otherwise wrap it into a procedure and use a variable – peterm Jun 21 '13 at 10:40
  • You're right, and I agree. But it's a challenge, right? :) Btw, don't try it in SQLFiddle.. for some reason the NEXTVAL and CURRVAL in same insert statement returns the same value (it doesn't work that way in the DB I use... maybe different version? IDK...). – ADTC Jun 21 '13 at 10:44