Insert multiple records in Oracle Database

Question

Example: INSERT INTO TABLE ( USERID, USERNAME) VALUES (1,"ok1"),(2,"ok2");

As i understood ,Multiple rows insertion not allowed in Oracle Database.

Please confirm me if any other alternatives for inserting multiple records into oracle DB with above format.

Gordon Linoff · Accepted Answer · 2020-07-30 02:08:16Z

2

Oracle only allows one row to be inserted at a time. So use two inserts:

INSERT INTO TABLE ( USERID, USERNAME)
    VALUES (1, 'ok1');

INSERT INTO TABLE ( USERID, USERNAME)
    VALUES (2, 'ok2');

Or use INSERT . . . SELECT:

INSERT INTO TABLE ( USERID, USERNAME)
    SELECT 1 'ok1' FROM DUAL UNION ALL
    SELECT 2, 'ok2' FROM DUAL;

answered Jul 30, 2020 at 1:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thanks . Yes - the above option is working . i have already tested . But looking for if any other option.

If you need load a lot of records from a flat file, sql loader is a good option. External tables is another option

One disadvantage with this is we cant use a sequnce.nextval as it is prohibited in union of select

@GordonLinoff can you tell me why your second method is not working? dbfiddle.uk/sB1BGlXQ