CODEWITHSUNDEEP
mysql
helle
helle

Reputation: 11650

How to insert many unique rows via INSERT in mysql?

You know, you can duplicate an entry in a table like so;

INSERT INTO `clubs`( `id_leagues`, `name`, `created`) SELECT id_leagues, name, created FROM clubs WHERE id = 36 LIMIT 1

..where my primary key "id" is an auto_increment integer

My question is, is there a way to create ... say 10 entries in a similar way, without using a loop in my php script. Maybe a loop in mysql?

thanks for help

UPDATE

It should insert the same column 10 times.

Upvotes: 1

Views: 382

Answers (2)

Minras
Minras

Reputation: 4346

If you wish to insert 10 records with the same conditions:

INSERT INTO `clubs`( `id_leagues`, `name`, `created`) 
SELECT id_leagues, name, created FROM clubs WHERE id = 36 LIMIT 10

And with another conditions:

INSERT INTO `clubs`( `id_leagues`, `name`, `created`) 
SELECT id_leagues, name, created FROM clubs WHERE id IN (36,37,38,40,45,55)

And in PHP if you queries are too complicated to use INSERT INTO ... SELECT, you can select required data, form a single bulk insert query and execute it:

$query = "SELECT id_leagues, name, created FROM clubs WHERE id = 36 LIMIT 1";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM));
$data = array_fill(0, 9, "('".mysql_real_escape_string($row[0])."', '".mysql_real_escape_string($row[1])."', '".mysql_real_escape_string($row[2])."')");

$query = "INSERT INTO `clubs`( `id_leagues`, `name`, `created`) VALUES " . implode(',', $data);

mysql_query($query);

Upvotes: 1

matzino
matzino

Reputation: 3564

No problem :) Select more than one tupel in your select statement.

INSERT INTO "table" ("col1", "col2", ...)
SELECT "col3", "col4", ...
FROM "table2"

Upvotes: 0

Related Questions