CODEWITHSUNDEEP
sqlsql-server
Ragnar
Ragnar

Reputation: 1582

How to insert multiple rows in same SQL Statement

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")

Upvotes: 0

Views: 3439

Answers (2)

Siyual
Siyual

Reputation: 16917

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'

Upvotes: 6

granadaCoder
granadaCoder

Reputation: 27904

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"

Upvotes: 1

Related Questions