CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Wali H
Wali H

Reputation: 83

Syntax error when trying to insert multiple rows in SQL?

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?

Upvotes: 0

Views: 5246

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

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');

Upvotes: 0

Related Questions