how to batch insert with auto_increase id using oracle

Question

batch insert data into oracle and the id is increased .how to deal ? i use the sql below all fails.

insert into USER_MULTIVALUED_ATTRIBUTES (ATTRIBUTEID, ORGANIZATIONID, 
ENTRYUUID,ATTRIBUTENAME, ATTRIBUTETYPE, ATTRIBUTESTATUS, ATTRIBUTEVALUE,
                                     USERBUCKETID)
(SELECT SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL ,'1','1','1','1','1','1',1  FROM 
DUAL) union all (SELECT SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL 
,'1','1','1','1','1','1',1 FROM DUAL)

another

INSERT ALL
INTO USER_MULTIVALUED_ATTRIBUTES
    (ATTRIBUTEID, ORGANIZATIONID, ENTRYUUID,ATTRIBUTENAME, ATTRIBUTETYPE, 
ATTRIBUTESTATUS, ATTRIBUTEVALUE,USERBUCKETID)
    VALUES(SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL ,'1','1','1','1','1','1',1)

INTO USER_MULTIVALUED_ATTRIBUTES
    (ATTRIBUTEID, ORGANIZATIONID, ENTRYUUID,ATTRIBUTENAME, ATTRIBUTETYPE, 
ATTRIBUTESTATUS, ATTRIBUTEVALUE,
     USERBUCKETID)
VALUES( SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL,'1','1','1','1','1','1',1)
    SELECT 1 FROM DUAL

first sql error: can not use sequence here

second sql error： Violation of unique constraints

oracle 11g

Possible duplicate of - [Inserting multiple rows with sequence in Oracle](https://stackoverflow.com/questions/31968093/inserting-multiple-rows-with-sequence-in-oracle) — Popeye, Jul 30 '19 at 08:59

Barbaros Özhan · Answer 1 · 2019-07-30T09:07:12.253

You don't need UNION ALL, use a SELECT statement with CONNECT BY LEVEL clause:

INSERT INTO USER_MULTIVALUED_ATTRIBUTES (ATTRIBUTEID, ORGANIZATIONID, 
ENTRYUUID,ATTRIBUTENAME, ATTRIBUTETYPE, ATTRIBUTESTATUS, ATTRIBUTEVALUE, USERBUCKETID)
SELECT SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL ,'1','1','1','1','1','1',1  
  FROM DUAL
CONNECT BY LEVEL <= N; --where N is a constant value which represents the number of rows to be inserted.

you can even prefer using LEVEL pseudocolumn instead of SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL as a increasing integer value starting from 1.

score 0 · Answer 2 · answered Jul 30 '19 at 14:10

If this is the actual issue then @Barbaros Özhan has given the perfect answer.

But, If this is just an example and using connect by is not feasible in your case then you can use deterministic function OR If creating the function is not feasible in your case then you can use the following whenever you need NEXTVAL of Sequence in such cases.

to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select <YOUR_SEQUENCE_NAME>.nextval as c '
      || 'from dual'))
  returning content))

So your code will look like below:

INSERT INTO USER_MULTIVALUED_ATTRIBUTES (
    ATTRIBUTEID,
    ORGANIZATIONID,
    ENTRYUUID,
    ATTRIBUTENAME,
    ATTRIBUTETYPE,
    ATTRIBUTESTATUS,
    ATTRIBUTEVALUE,
    USERBUCKETID
)
    ( SELECT
        to_number(xmlquery('/ROWSET/ROW/C/text()'
        passing xmltype(dbms_xmlgen.getxml(
          'select SEQ_MAMULTIVALUEDATTRIBUTES.nextval as c '
          || 'from dual'))
      returning content)), -- Same for the case in INSERT ALL
        '1',
        '1',
        '1',
        '1',
        '1',
        '1',
        1
    FROM
        DUAL
    )
    UNION ALL
    ( SELECT
        SEQ_MAMULTIVALUEDATTRIBUTES.NEXTVAL,
        '1',
        '1',
        '1',
        '1',
        '1',
        '1',
        1
    FROM
        DUAL
    );

Cheers!!

how to batch insert with auto_increase id using oracle

2 Answers2