Incrementing a value in a mutliple row INSERT statement

Question

This should (hopefully) be a fast one, unfortunately I am a little bit slow today and need your help.

I've basically wrote an INSERT statement with the following structure in order to insert multiple rows with a single statement:

INSERT INTO TABLE_TO_INSERT_INTO 
SELECT 
    -- ...,
    -- ...,
    -- ...,
    (SELECT MAX(ID)+1 FROM SOME_TABLE) As ID,
    -- ...,
    -- ...,
FROM
    (
        -- Subqueries and Joins here
        -- ...
    ) ;

This generally works perfectly fine, but for one part: the ID.

It has got to be unique (a constraint makes sure of that), but with the current way of selecting the ID that is not the case, as it uses the selected value for all rows that are inserted via the above statement.

Is there any way of forcing a reevalution of the (SELECT MAX(ID)+1 FROM SOME_TABLE) As ID part after every row inserted?

I don't really want to use a trigger, but instead implement it on a pure SQL basis.

I hope someone has a solution, I am at a loss today and can't really see it. Thanks in advance!

Also see [this question](http://stackoverflow.com/q/17210835/266304) for some of the issues with using `max(id) + 1` generally. — Alex Poole, Jul 16 '15 at 10:50

score 2 · Accepted Answer · answered Jul 16 '15 at 09:42

2

As you are using Oracle, then I would suggest you to use SEQUENCE

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

In your query

SELECT customers_seq.nextval from yourTable;

The above query guarantee to return unique value after each execution.

answered Jul 16 '15 at 09:42

Ravi

30,829
42
119
173

If It helped. Then, feel free to accept the answer. :) – Ravi Jul 16 '15 at 09:45
Will do 4 minutes from now ;-) – daZza Jul 16 '15 at 09:45
Why use `nocache`? Oh, I see you copied that example from the docs... weird they would promote that. [Tom wants higher caches!](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705#371520000346798513) – Alex Poole Jul 16 '15 at 09:54
@jWeaver Is it possible to use a subquery instead of `1000` (or any other hardcoded number for that matter) in the `START WITH` part? I keep getting an error when trying to do that :( – daZza Jul 16 '15 at 09:59
1

@daZza - no; [you would have to do that using dynamic SQL](http://stackoverflow.com/q/19666293/266304). – Alex Poole Jul 16 '15 at 10:02
@AlexPoole Correct. It was taken from doc. To just show, the sequence syntax. – Ravi Jul 16 '15 at 10:06

score 0 · Answer 2 · answered Jul 16 '15 at 09:42

Unless you are inserting into SOME_TABLE (which you aren't), (SELECT MAX(ID)+1 FROM SOME_TABLE) As ID will always return the same value because the MAX(ID) is not affected by additional rows in TABLE_TO_INSERT_INTO.

You could in theory use some kind of row number offset plus your MAX(ID) value BUT I would strongly advise against it. You would be better off using a field which has a seed and increment computed value as per best practice as this will work well with distributed transactions.

score 0 · Answer 3 · answered Jul 16 '15 at 09:45

0

you could use ROWNUM, but be carefull when sorting, then you need an extra subquery around your original SELECT

(SELECT MAX(ID)+ROWNUM FROM SOME_TABLE) As ID,

answered Jul 16 '15 at 09:45

Joram

3,166
1
22
29

score -2 · Answer 4 · answered Jul 16 '15 at 09:42

-2

In SQL 2012 you can use sequence and use same while inserting data.

You can refer following: [https://msdn.microsoft.com/en-us/library/ff878091.aspx][1]

answered Jul 16 '15 at 09:42

Developer Dev

1
3

That isn't entirely helpful for a question tagged as Oracle. – Alex Poole Jul 16 '15 at 09:50

Incrementing a value in a mutliple row INSERT statement

4 Answers4