Inserting multiple values with Oracle Insert Into...Values

Question

I am trying to run the following command in Oracle 11g but keep getting an error of 'commmand not properly ended'. I am new to Oracle and I haven't been able to find anything about insert multiple value sets (unless they are selected from a table which in this case they are not)....

INSERT INTO category (catcode, catdesc)
VALUES ('BUS', 'BUSINESS'),
    ('CHN', 'CHILDREN'),
    ('COK', 'COOKING'),
    ('COM', 'COMPUTER'),
    ('FAL', 'FAMILY LIFE'),
    ('FIT', 'FITNESS'),
    ('SEH', 'SELF HELP'),
    ('LIT', 'LITERATURE'),
    ('CHN', 'CHILDREN'),
    ('BUS', 'BUSINESS');

If this syntax is completely off, then how can I insert multiple values in one statement?

Dba · Accepted Answer · 2014-02-05 03:10:30Z

9

Try like this,

INSERT ALL 
     INTO category (catcode, catdesc) VALUES ('BUS', 'BUSINESS')
     INTO category (catcode, catdesc) VALUES ('CHN', 'CHILDREN')
     INTO category (catcode, catdesc) VALUES ('COK', 'COOKING')
     INTO category (catcode, catdesc) VALUES ('COM', 'COMPUTER')
     INTO category (catcode, catdesc) VALUES ('FAL', 'FAMILY LIFE')
     INTO category (catcode, catdesc) VALUES ('FIT', 'FITNESS')
     INTO category (catcode, catdesc) VALUES ('SEH', 'SELF HELP')
     INTO category (catcode, catdesc) VALUES ('LIT', 'LITERATURE')
     INTO category (catcode, catdesc) VALUES ('CHN', 'CHILDREN')
     INTO category (catcode, catdesc) VALUES ('BUS', 'BUSINESS')
SELECT * FROM DUAL;

Refer here for more in detail.

answered Feb 5, 2014 at 3:10

Dba

6,6791 gold badge26 silver badges33 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2014-02-05 03:51:24Z

5

You can always use insert . . . select syntax:

INSERT INTO category (catcode, catdesc)
    select 'BUS', 'BUSINESS' from dual union all
    select 'CHN', 'CHILDREN' from dual union all
    select 'COK', 'COOKING' from dual union all
    select 'COM', 'COMPUTER' from dual union all
    select 'FAL', 'FAMILY LIFE' from dual union all
    select 'FIT', 'FITNESS' from dual union all
    select 'SEH', 'SELF HELP' from dual union all
    select 'LIT', 'LITERATURE' from dual union all
    select 'CHN', 'CHILDREN' from dual union all
    select 'BUS', 'BUSINESS' from dual;

answered Feb 5, 2014 at 3:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Inserting multiple values with Oracle Insert Into...Values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related