Is it possible to insert the same values at once (Mysql)?

Question

If i have a table that Example is and one coloumn in it that (for example) colour is how i can do something like;

    INSERT INTO Example VALUES ('Red','Black','Green');

instead of writing the code 3 times seperatly for each value?

score 3 · Answer 1 · answered Jun 13 '13 at 22:53

3

INSERT INTO Example (column_name) VALUES ('Red'),('Black'),('Green');

answered Jun 13 '13 at 22:53

000

Mike Brant · Answer 2 · 2016-02-10T16:18:16.153

3

Yes you can do this, but your format is off.

INSERT INTO `Example` (`color`)
VALUES
('Red'),
('Black'),
('Green')

Each row must be separated by a comma with values for the row in parenthesis.

edited Feb 10 '16 at 16:18

answered Jun 13 '13 at 22:54

Mike Brant

score 0 · Answer 3 · answered Jun 13 '13 at 23:01

0

Here is another way that will work with any database.

insert into example
select 'Red'
from SomeSmallTable
union
select 'Blue'
from SomeSmallTable
union
select 'Green'
from SomeSmallTable

answered Jun 13 '13 at 23:01

Dan Bracuk

what. is `SomeSmallTable` as alias of `dual`? – 000 Jun 13 '13 at 23:01
1

The `from` clauses aren't needed. From the docs: "DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced." – 000 Jun 13 '13 at 23:03
Dual is not available with all databases. Plus not all databases allow select queries without where clauses. – Dan Bracuk Jun 14 '13 at 01:04
Hi Dan, do some tests with your code and you'll see something wrong. – leonardo_assumpcao Jun 14 '13 at 02:05
I agree with Joe - in MySQL you don't need the `from` clause at all, so the following will work `insert into example select 'Red' union select 'Blue' union select 'Green'` – Tim Rogers Nov 03 '17 at 20:59

3 Answers3