How to insert multiple rows in same SQL Statement

Question

I run this Statement:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES ((001, 002), ("f", "Doe"), ("f", "John"), ("6256 german Ave S", "1234 random ave"), ("berlin", "Rondomville"))

and get this error:

Operand should contain 1 column(s)

I don't know what exactly i'm doing wrong here.

If I run the Statement below everthing works fine:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES(001, "f", "a", "6254 german Ave S", "berlin")

Group the values by rows, not by columns.

Devon Bessemer
– Devon Bessemer

2016-01-15 19:28:05 +00:00
Commented Jan 15, 2016 at 19:28 — Devon Bessemer
– Devon Bessemer, Commented Jan 15, 2016 at 19:28

Siyual · Accepted Answer · 2016-01-15 19:28:21Z

6

The correct syntax for this is:

INSERT INTO Persons 
            (PersonID, LastName, FirstName, Address, City)
VALUES      (1, 'f', 'f', '6256 german Ave S', 'berlin'),
            (2, 'Doe', 'John', '1234 random ave', 'Rondomville')

Alternatively, you can do a INSERT INTO ... SELECT with a UNION ALL:

INSERT INTO         Persons 
                    (PersonID, LastName, FirstName, Address, City)
SELECT              1, 'f', 'f', '6256 german Ave S', 'berlin'
UNION ALL SELECT    2, 'Doe', 'John', '1234 random ave', 'Rondomville'

answered Jan 15, 2016 at 19:28

Siyual

17k8 gold badges47 silver badges60 bronze badges

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

3 Comments

granadaCoder Over a year ago

Your alternate answer looks a lot like my answer. Just sayin'

Siyual Over a year ago

It's a very common query to use. Just sayin'.

Ragnar Over a year ago

Thanks, it's always the simple errors that cause the most frustration

granadaCoder · Accepted Answer · 2016-01-15 19:27:45Z

1

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
Select 001, "f", "a", "6254 german Ave S", "berlin"
UNION ALL
Select 002, "f", "a", "333 deutch strasse", "freiburg"

answered Jan 15, 2016 at 19:27

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

1 Comment

granadaCoder Over a year ago

The reason I prefer this over the "values" way...is that I can select just the "select" portion of the code and run it. In a stored procedure, I could temporarily comment out the "insert" part and get the select query to run. You cannot do that with the "values" syntax.

Collectives™ on Stack Overflow

How to insert multiple rows in same SQL Statement

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related