Syntax error when trying to insert multiple rows in SQL?

Question

I am trying to store multiple values into a table with two columns in a single command. Here is my command,

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

I get a red underline after my first pair of values where I have the comma. What am I doing wrong?

Check this: https://stackoverflow.com/a/93724/2348125 – Amir Molaei Mar 18 '19 at 19:28 — Amir Molaei, Mar 18 '19 at 19:28

score 1 · Accepted Answer · answered Mar 18 '19 at 19:15

I would start by listing columns:

INSERT INTO CATEGORY (<colname1>, <colname2)
    VALUES ('BUS','BUSINESS'), ('CHN', 'CHILDREN'), ('COK', 'COOKING'), 
           ('COM', 'COMPUTER'), ('FAL', 'FAMILY LIFE'), ('FIT', 'FITNESS'), 
           ('SEH', 'SELF HELP'), ('LIT', 'LITERATURE');

Not all databases support VALUES with multiple rows. So that could be the cause of your problem.

If you are using, say, Oracle that doesn't support this, you can use UNION ALL:

INSERT INTO CATEGORY (<colname1>, <colname2)
    SELECT 'BUS', 'BUSINESS' FROM DUAL UNION ALL
    SELECT 'CHN', 'CHILDREN' FROM DUAL UNION ALL
    . . .
    SELECT 'LIT', 'LITERATURE' FROM DUAL;

score 0 · Answer 2 · answered Mar 18 '19 at 19:13

place your column name explicitly if more column exist in your table

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

Syntax error when trying to insert multiple rows in SQL?

2 Answers2

Related